A lot of time we need truncate (trim) a datetime value.
E.g.. we use GETDATE() function, but we need only the Date portion of the value and not the time. So we need to set the time to the midnight 00:00:00.000.
There are several possibilities to to this and and mostly I see using the CONVERT function with the style parameter converting to varchar and than back to datetime.
CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112))
Although it is possible to use this syntax, it is not the best solution.
Much better is to use DATEADD and DATEDIFF functions.
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
This counts a difference in days from zero and than it adds it back to zero, so we receive the value trimmed to whole days.
By simple modification we can adjust this to the first of month, last of month, whole hours, minutes etc. by simply changing the datepart.
--First of Month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) --Last of Month: DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0) - 1 --First of Year: DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0) --Last of Year: DATEADD(year, DATEDIFF(year, 0, GETDATE()) + 1, 0) – 1 --Hour: DATEADD(hour, DATEDIFF(hour, 0, GETDATE()) + 1, 0) – 1 --Minute: DATEADD(minute, DATEDIFF(minute, 0, GETDATE()) + 1, 0) – 1
Not only by this format is easier to achieve needed results but it’s even around two times faster!
We can compare the speed by simple speed test script:
DECLARE @startTime datetime
DECLARE @truncDate datetime
DECLARE @cnt int
DECLARE @msg varchar(400)
DATEADD and DATEDIFF
SELECT
@cnt = 0,
@startTime= GETDATE()
WHILE @cnt < 1000000
SELECT
@truncDate = CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 112)),
@cnt = @cnt + 1
SELECT
@msg = 'Trucation using CONVERT: resutl = ' + CONVERT(varchar(30), @truncDate) +
' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg
SELECT
@cnt = 0,
@startTime= GETDATE()
WHILE @cnt < 1000000
SELECT
@truncDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0),
@cnt = @cnt + 1
SELECT
@msg = 'Trucation using DATEDIFF & DATEADD: resutl = ' + CONVERT(varchar(30), @truncDate) +
' : Took: ' + CONVERT(varchar, DATEDIFF(MILLISECOND, @startTime, GETDATE())) + ' ms'
PRINT @msg
Pavel's SQL Pages 
I tested this in 2005 and 2008, and the results weren’t that good…
2005:
Trucation using CONVERT: resutl = Oct 31 2012 12:00AM : Took: 29590 ms
Trucation using DATEDIFF & DATEADD: resutl = Oct 31 2012 12:00AM : Took: 29886 ms
2008:
Trucation using CONVERT: resutl = Oct 31 2012 12:00AM : Took: 29146 ms
Trucation using DATEDIFF & DATEADD: resutl = Oct 31 2012 12:00AM : Took: 29693 ms
Hi,
on what configuration you have tested this? Because a normal run times on quad core processor is about 700-900 ms for the DATEADD-DATEDIFF and about 1400-1800 for the CONVERT version.
Your execution times are quite long.