What is the best way to truncate a date in SQL Server?

To round to the nearest whole day, there are three approaches in wide use. The first one uses datediff to find the number of days since the 0 datetime. The 0 datetime corresponds to the 1st of January, 1900. By adding the day difference to the start date, you've rounded to a whole day;

select dateadd(d, 0, datediff(d, 0, getdate()))

The second method is text based: it truncates the text description with varchar(10), leaving only the date part:

select convert(varchar(10),getdate(),111)

The third method uses the fact that a datetime is really a floating point representing the number of days since 1900. So by rounding it to a whole number, for example using floor, you get the start of the day:

select cast(floor(cast(getdate() as float)) as datetime)

To answer your second question, the start of the week is trickier. One way is to subtract the day-of-the-week:

select dateadd(dd, 1 - datepart(dw, getdate()), getdate())

This returns a time part too, so you'd have to combine it with one of the time-stripping methods to get to the first date. For example, with @start_of_day as a variable for readability:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(dd, 1 - datepart(dw, @start_of_day), @start_of_day)

The start of the year, month, hour and minute still work with the "difference since 1900" approach:

select dateadd(yy, datediff(yy, 0, getdate()), 0)
select dateadd(m, datediff(m, 0, getdate()), 0)
select dateadd(hh, datediff(hh, 0, getdate()), 0)
select dateadd(mi, datediff(mi, 0, getdate()), 0)

Rounding by second requires a different approach, since the number of seconds since 0 gives an overflow. One way around that is using the start of the day, instead of 1900, as a reference date:

declare @start_of_day datetime
set @start_of_day = cast(floor(cast(getdate() as float)) as datetime)
select dateadd(s, datediff(s, @start_of_day, getdate()), @start_of_day)

To round by 5 minutes, adjust the minute rounding method. Take the quotient of the minute difference, for example using /5*5:

select dateadd(mi, datediff(mi,0,getdate())/5*5, 0)

This works for quarters and half hours as well.

If you are using SQL Server 2008, you can use the new Date datatype like this:

select cast(getdate() as date)

If you still need your value to be a DateTime datatype, you can do this:

select cast(cast(getdate() as date) as datetime)

A method that should work on all versions of SQL Server is:

select cast(floor(cast(getdate() as float)) as datetime)

Try:

SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

UPDATE: answer on the second question: for years you could use a little bit modified version of my answer:

SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)

for quarter:

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

and so on.

I checked, up to minutes - it's OK. But on seconds I've got an overflow message:

Difference of two datetime columns caused overflow at runtime.

One more update: take a look to the following answer to the same question