THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

T-SQL Tuesday #001: My Datetime FAQ

I am rising to Adam's challenge for his very first T-SQL Tuesday, and blogging about datetime issues. Since I just finished a 9-hour teaching day in Stockholm, and I'm incredibly jetlagged, I decided to keep this simple. I'm very much looking forward to what everyone else is going to say!

For as long as I've been providing TSQL support, starting over 22 years ago while working for Sybase,  there have been confusions about datetime data storage and retrieval. I have seen the same questions over and over for all of those years, so I am listing the all-time most common datetime questions here. My FAQ stands for "Frequently ANSWERED Questions"! To keep the post simple, I am not going into complete detail for every answer. If you want more details you can read one of my books.  I am also just listing the tip of the iceberg in terms of common datetime questions.

1. I have my dates stored like this: "March 4, 1948" but SQL Server is returning them like this: "1948-03-04 13:17:32.120".  How can I get the dates returned in the manner I stored them?

This question actually can't be answered as written, because it is NOT true that a date could be stored as "March 4, 1948".  A character string could be stored that way, but not a date. Internally, datetime data is stored in a format you never see displayed, no matter how you enter the datetime value. It is stored as two integers, the first being the number of days before or after the base date of "January 1 1900" and the second integer being the number of milliseconds after midnight.

You can actually see these two parts if you convert a datetime value to a binary string of 8 hexadecimal bytes. The first 4 hexadecimal bytes are the number of days before or after the base date, and the second 4 bytes are the number of clock ticks after midnight. You can then convert these 4-byte hexadecimal strings to integers.

You can see the component parts of the current date and time by using the system function getdate(). I first store the current date and time in a local variable so I can be sure I'm using the same value for both computations.

DECLARE @today datetime
SELECT @today = getdate()

SELECT @today
SELECT CONVERT (varbinary(8), @today)
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 1, 4))
SELECT CONVERT (int, SUBSTRING (CONVERT (varbinary(8), @today), 5, 4))

The way a datetime value is displayed depends on several factors, including the tool you are using and your machine's regional settings. If you want to control the way a datetime value is displayed, you can convert it to a character string. 

2. Why doesn't this query find any rows:  SELECT * FROM orders WHERE orderdate = 'March 4, 2009'

The most like reason is because datetime data always includes a time portion as well as a date portion. Unless your orders were entered at a time of midnight (the default), they won't match the datetime value shown. (This answer frequently leads to the next question.)

3. Why can't I store just a date or just a time, why do I always need both?

The simple answer, prior to SQL Server 2008 is "just because". That is the way SQL Server works. Microsoft has been promising individual date and time datatypes for several versions now, and they finally showed up in SQL Server 2008, as well as a couple of other new datetime datatypes, plus the ability to specify a precision for fractional part a second.

4.  Why doesn't this query show me how old I am? SELECT datediff(yy, @my_birthdate, getdate() )

Arithmetic with datetime values works only with whatever datepart is specified as the first parameter and does no rounding. Since my first parameter is yy, for year, the datediff simply subtracts the year component of my birth year from the year component of today's date. If I haven't had my birthday yet, it will show me how old I will be on my birthday this year, which will not be my current age. And since birthday is almost at the end of the year, for most of the year I will get an incorrect number.

5. Why can't I store dates earlier than 1753 using the datetime datatype?

This was done for historical reasons, and started with the original Sybase specification for the datetime datatype. In what we sometimes refer to as the western world, we have had two calendars in modern time: the Julian and the Gregorian calendars. These calendars were a number of days apart (depending on which century you look at), so when a culture that used the Julian calendar moved to the Gregorian calendar, they dropped between 10 to 13 days from the calendar. Great Britain made this shift in 1752, and in that year, September 2nd was followed by September 14th. Sybase decided not to stored dates earlier than 1753 because the date arithmetic functions would be ambiguous. However, other countries made the change at other times, and in Turkey the calendar was not shifted until 1927.

The new date and datetime2 times in SQL Server 2008 do allow us to store dates back to 0001-01-01.

For one of the best articles on the web about datetime issues in SQL Server, see Tibor Karaszi's website:

http://www.karaszi.com/SQLServer/info_datetime.asp

I love the calendars in Europe, especially Germany and Sweden, because the first part of the word is my name!

~Kalen

Published Tuesday, December 08, 2009 11:49 AM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jonathan Kehayias said:

I really love the explaination for #5 that you provided.  I never really thought much about it, but the history behind the reason is very interesting, at least to me.  Thanks for sharing that.

December 9, 2009 3:16 PM
 

gbn said:

BOL for SQL Server 2005 states "The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight."

December 11, 2009 2:51 AM
 

Kalen Delaney said:

Hi, gbn, I actually knew that but didn't want to have to explain about 'ticks'. I did mention that I was not going to be as technical as I could have been, and told people they could read my books if they like.. I do talk about ticks and the 1/300th of a second time units there.

Thanks

Kalen

December 11, 2009 5:11 PM
 

pwehland said:

How old am I in earth years?

SELECT datediff(dd, @my_birthdate, getdate() ) / 365.25

How old am I in Martian years?

SELECT datediff(dd, @my_birthdate, getdate() ) / 365.25

Q: Why is this the same number?

A: You are confusing Years with Planetary Orbital Periods! :-)

December 11, 2009 9:35 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement