Aaron Akin

March 19, 2009

Stripping the time from a DATETIME value

Filed under: SQL,TSQL — Aaron Akin @ 8:39 pm

Many times there is a need to strip the time off of a DATETIME value so you are left with the date only. I’ve seen a few different ways to handle this. To clarify, when I say a DATETIME value with no time, I really mean midnight (00:00:00.000) on that date.

Converting to VARCHAR

One way to remove the time from a DATETIME value is to convert it to a VARCHAR data type. This is handy when you need the date returned as a VARCHAR value and when you need the option to return the date in any number of different formats, with or without the century (i.e. 2009 vs 09). The full list of the different formats that are available using CONVERT is located in Books Online. Here is an example of a few the different formats.


     , CONVERT(VARCHAR,@dt,101) AS Date101
     , CONVERT(VARCHAR,@dt,12) AS Date12
     , CONVERT(VARCHAR,@dt,112) AS Date112
Date1      Date101    Date12     Date112
---------- ---------- ---------- ----------
03/19/09   03/19/2009 090319     20090319

One thing to consider when comparing a DATETIME value to another DATETIME value that has been converted to VARCHAR, you should always convert it back to DATETIME. This is called an explicit conversion when you use CAST or CONVERT to change a value to a different data type. The comparison will still work properly without the explicit conversion, but this will cause SQL Server to convert it at runtime, called an implicit conversion, putting unneccesary overhead on the server.


Another way to remove the time from a DATETIME value is to use a combination of the DATEADD and DATEDIFF functions. This is a good way to not only strip the time off of a particular DATETIME value, but you can also use that value to get other dates without the time. Here is an example of a few different dates that can be returned using the current date as a reference point.


     , DATEADD(DAY,DATEDIFF(DAY,0,@dt),-1) AS Yesterday
     , DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0) AS FirstOfThisMonth
     , DATEADD(MONTH,DATEDIFF(MONTH,-1,@dt),-1) AS LastOfThisMonth
     , DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),-1) AS LastOfPrevMonth
     , DATEADD(YEAR,DATEDIFF(YEAR,0,@dt),0) AS FirstOfYear
Today               Yesterday           FirstOfThisMonth    LastOfThisMonth     LastOfPrevMonth     FirstOfYear
------------------- ------------------- ------------------- ------------------- ------------------- -------------------
2009-03-19 00:00:00 2009-03-18 00:00:00 2009-02-28 00:00:00 2009-03-31 00:00:00 2009-02-28 00:00:00 2009-01-01 00:00:00

ODBC Date/Time Escape Clauses

Another interesting method I found recently involves ODBC date and time escape clauses. I don’t really know much about these, but I thought they were intriguing. There are 3 extensions, one for date (d), time (t), and timestamp (ts). The only one I thought might be useful was the time extension. Here’s an example of using this to retrieve today’s date with no time value.

SELECT {t '00:00:00'} AS Today
2009-03-19 00:00:00

I could see this being handy if you only ever wanted to get the current date at midnight, but I haven’t found a way to pass in a variable or column to this, so it is limited to the current date only. Just for kicks, here’s how you would use the date and timestamp escape clauses.

SELECT {ts '2009-03-19 00:00:00'}
     , {d '2009-03-19'}

Both of these will return today’s date at midnight as well. I’d be interested to see if anyone knows any other uses for these.



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: