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.

February 19, 2009

Deleting Duplicate Records

Filed under: TSQL — Aaron Akin @ 6:42 pm

I was helping someone out today on Experts-Exchange who had a quite a few duplicate records in a table that he wanted to get rid of.  Here’s an example of the data.


LastName FirstName

-------- ---------
Smith    John
Smith    John
Smith    John
Jackson  Tom
Jones    Sarah
Jones    Sarah
Williams Jessica

As you can see, there is nothing in this table that makes each row unique, and therefore you cannot delete one of the records for John Smith without deleting both records.


February 18, 2009

SSIS 2005 Connections from SSMS 2008

Filed under: SQL 2005,SQL 2008,SSIS,SSMS — Aaron Akin @ 5:17 pm

After I first installed SQL 2008, I tried to connect to SSIS 2005, and to my surprise, received the error, “Connect to SSIS Service on machine xxx failed: Class not registered.”  Thanks for the descriptive error message, Microsoft.

Cannot Connect to Server

Apparently, you cannot connect to SSIS 2005 from within SSMS 2008.  I found a feedback ticket with Microsoft that confirms the issue, though I wasn’t completely clear if they are planning on fixing it or not.

So, for those of you who installed SSMS 2008, make sure you keep SSMS 2005 installed as well if you still have SSIS 2005 packages out there…even if it is a pain to have to switch back and forth.


Next Page »

Blog at WordPress.com.