DateTime

DATETIME truncation methods speed comparison

Pavel Pawlowski SQL Server, T-SQL , ,

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
Advertisement