SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

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
# of Orders Order Date
32 2008-07-28 00:00:00.000
31 2008-07-29 00:00:00.000
23 2008-07-30 00:00:00.000
40 2008-07-31 00:00:00.000

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)
# of Orders Order Date
32 20080728
31 20080729
23 20080730
40 20080731

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)
# of Orders Order Date
32 7/28/2008
31 7/29/2008
23 7/30/2008
40 7/31/2008

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'
GetDate GetTime
2009-12-05 17:10:54.430 17:10

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)
# of Orders Sunday of the Week Day# of Week Order Date
24 2008-07-20 00:00:00.000 6 20080725
32 2008-07-20 00:00:00.000 7 20080726
29 2008-07-27 00:00:00.000 1 20080727
32 2008-07-27 00:00:00.000 2 20080728
31 2008-07-27 00:00:00.000 3 20080729
23 2008-07-27 00:00:00.000 4 20080730
40 2008-07-27 00:00:00.000 5 20080731

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

One Comment

  1. […] Nelson kept things quite true to theme, posting a grab-bag of “Date, Time, tricks with the DateTime Data Type”. His post includes a number of snippets—perhaps you’ll find something that will help you […]