DATETIME truncation methods speed comparison

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
Advertisements

2 thoughts on “DATETIME truncation methods speed comparison

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s