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.



March 18, 2009

Finding the major version number of your SQL Server instance

Filed under: SQL — Aaron Akin @ 8:38 pm

Occasionally, I need to determine which version of SQL Server I am connected to within a script so that I can write the proper code for that version. I’m never really concerned with the build number or which service pack I have installed. Instead, all I want is the major version number (7, 8, 9, 10, etc).

One of the difficult things about something that seems like it should be so simple is that some of the ways to check the version number only exist on certain versions. Other ways give you way more info than you really need.

Recently, I was digging through a system stored procedure, which is always the best place to find cool code, and I came across the undocumented system function @@MICROSOFTVERSION which is used internally by Microsoft.

It returns a seemingly random number, but you can use a bitwise AND operator to find the major version number.


This will return the following based on your version of SQL Server:

7 (SQL 7 )
8 (SQL 2000)
9 (SQL 2005)
10 (SQL 2008)

This will work on any version of SQL Server as of now, but since it is undocumented, it’s always possible that it could be removed in the future.


Nested Common Table Expressions

Filed under: CTE,SQL,SQL 2005,SQL 2008,TSQL — Aaron Akin @ 8:01 pm

If you’ve looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can’t define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner.

Blog at WordPress.com.