T-SQL Tuesday Date, Time, tricks with the DateTime Data Type

Adam Machanic told us all about a new craze sweeping the SQL Blogosphere Nation last week and that is T-SQL Tuesdays. Real quick, the way it works is that on Patch-Tuesday everyone who’s got something to say about the topic of the month releases a blog post about it and the “Host” of the topic recaps all of the various blog posts in a blog at their site. OK now onto the topic.

Because I think there might be a few people bolgging about this today I will keep min short-and-sweet and go over 5 main points of the Old-School DateTime data type.

Before I start I want to remind you that DBAs Data Professionals are a little different, a lot of us think in terms of Largest to Smallest unit when we think of DateTime; which makes it into the way that you think of a standard number. Right now it is “200912051505” or “2009-12-05 15:05”

Selecting and aggregating using Styles all of the rows in a table can be a little problematic if you are selecting a date that looks something like this one “2009-12-05 15:05”. One of my favorite ways to get around this is with the CONVERT function and changing it to a VARCHAR and then using a style. Here’s what I usually do.

Run this query in AdventureWorks and you’ll notice that it also pulls in time which might be something that we want to avoid:

SELECT COUNT(*) AS '# of Orders'
 , OrderDate AS 'Order Date'
  FROM Sales.SalesOrderHeader
 GROUP BY OrderDate
 ORDER BY OrderDate

So what I do is CONVERT it to VARCHAR and then add Style 112 to it

SELECT COUNT(*) AS '# of Orders'
 , CONVERT(VARCHAR, OrderDate, 112) AS 'Order Date'
  FROM Sales.SalesOrderHeader
 GROUP BY CONVERT(VARCHAR, OrderDate, 112)
 ORDER BY CONVERT(VARCHAR, OrderDate, 112)

Now this isn’t overly readable but it sorts great. Another problem with it is that it doesn’t paste into Excel real well. For pasting into Excel and having it quickly recognize it as a DateTime field I use Style 110 but there’s a catch. Style 110 pastes into Excel fine but it doesn’t sort properly so I end up having to keep my Style 112 column so that I can sort on it.

SELECT COUNT(*) AS '# of Orders'
  , CONVERT(VARCHAR, OrderDate, 110) AS 'Order Date'
  , CONVERT(VARCHAR, OrderDate, 112) AS 'Order Sort'
  FROM Sales.SalesOrderHeader
 GROUP BY CONVERT(VARCHAR, OrderDate, 110)
  , CONVERT(VARCHAR, OrderDate, 112)
 ORDER BY CONVERT(VARCHAR, OrderDate, 112)

Selecting just the time with Style 108 is another trick I have used in the past. As a quick and easy way to extract just the time out of a DateTime field is to CONVERT it to VARCHAR making sure to specify a length of 5. (Now if you also want the seconds you need to make the length 8.)

SELECT GETDATE() AS 'GetDate', CONVERT(VARCHAR(5)
 , GETDATE(), 108) AS 'GetTime'

And now for my final trick
Selecting the date of the Sunday of the Week (or any recurring day of the week)

Sometimes – especially in Business Intelligence – you want to group data by week but you don’t want to use DATEPART (WK, OrderDate) because that produces a number and you might want something more like an actual date. In this case what I typically do is just use this: DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate)
(Now you can pick the Saturday of that week just as easily by swapping out that 1 for a 7.)

SELECT COUNT(*) AS '# of Orders'
 , DATEPART (WK, OrderDate)
 , DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate) AS 'Sunday of the Week'
 , DATEPART(WEEKDAY, OrderDate) AS 'Day# of Week'
 , CONVERT(VARCHAR, OrderDate, 112) AS 'Order Date'
  FROM Sales.SalesOrderHeader
 GROUP BY DATEPART (WK, OrderDate)
 , CONVERT(VARCHAR, OrderDate, 112)
 , DATEADD(DAY, 1-DATEPART(WEEKDAY, OrderDate), OrderDate)
 , DATEPART(WEEKDAY, OrderDate)
 ORDER BY CONVERT(VARCHAR, OrderDate, 112)

You can check out the rest of the blogs for this T-SQL Tuesday topic right here: http://sqlblog.com/blogs/adam_machanic/archive/2009/12/09/t-sql-tuesday-001-the-roundup.aspx or do a search on Twitter and look for the hash #TSQL2sDay.
I hope this helps a few people out, as always if you have any questions please comment.
TSQL2sDay