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

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.


January 21, 2009

Recursive Common Table Expressions – Part 2

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 7:48 pm

In my last post, Recursive Common Table Expressions – Part 1, I gave a brief overview of common table expressions (CTE) and gave some examples on how to use them to write recursive queries.  The first two examples showed how to find the missing numbers, or dates, in a sequence.  It was assumed that you already had a table with a list of numbers or dates in it.  The examples in this post will expand on the first two examples to show you how to build a range of numbers or dates given a starting and ending point.


January 20, 2009

Recursive Common Table Expressions – Part 1

Filed under: CTE,SQL,SQL 2005,TSQL — Aaron Akin @ 6:43 pm


I recently had a project that required me to find the number of employees who were scheduled for every 15 minute increment, for every business day for a week, and for many stores.  Yikes!  Sure, I could use tons of loops and temp tables to do this, but I’m all about set based queries whenever possible…and I knew this was possible and I love a challenge!

It finally occurred to me that I could use the self-referencing (recursive) capabilities of Common Table Expressions (introduced in SQL 2005) to the mimic loops I needed to accomplish my goal.

I took some of the recursive CTE queries that I came up with for my project and turned them into 4 examples.  It was quite a bit of info, so I’ll be spreading them across 2 posts.


Blog at WordPress.com.