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 OrdersOrder Date
322008-07-28 00:00:00.000
312008-07-29 00:00:00.000
232008-07-30 00:00:00.000
402008-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 OrdersOrder Date
3220080728
3120080729
2320080730
4020080731

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 OrdersOrder Date
327/28/2008
317/29/2008
237/30/2008
407/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'
GetDateGetTime
2009-12-05 17:10:54.43017: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 OrdersSunday of the WeekDay# of WeekOrder Date
242008-07-20 00:00:00.000620080725
322008-07-20 00:00:00.000720080726
292008-07-27 00:00:00.000120080727
322008-07-27 00:00:00.000220080728
312008-07-27 00:00:00.000320080729
232008-07-27 00:00:00.000420080730
402008-07-27 00:00:00.000520080731

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

Please Share This:

You may also like:

One Response

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner

%d bloggers like this: