MSSQL: 4 methods to get datetime without time part

There are 4 methods to truncate time part of the datetime value:

  • cast(convert(varchar, d, 101) as datetime)
    Avg. execution time: 1470
  • cast((str( year( d ) ) + ‘/’ + str( month( d ) ) + ‘/’ + str( day( d ) ) ) as datetime )
    Avg. execution time: 6373
  • cast(floor( cast( d as float ) ) as datetime )
    Avg. execution time: 554
  • dateadd(day, datediff(day, 0, d), 0)
    Avg. execution time: 525

As we can see last one is the most efficient way.
Because it doesn’t use string operations but numeric ones.

Advertisements

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

%d bloggers like this: