Why You Should Never Use DATETIME Again!
Dates, we store them everywhere, DateOrdered, DateEntered, DateHired, DateShipped, DateUpdated, and on and on it goes. Up until and including SQL Server 2005, you really didn’t have much choice about how you stored your date values. But in SQL Server 2008 and higher you have alternatives to DateTime and they are all better than the original.
DATETIME stores a date and time, it takes 8 bytes to store, and has a precision of .001 seconds
In SQL Server 2008 you can use DATETIME2, it stores a date and time, takes 6-8 bytes to store and has a precision of 100 nanoseconds. So anyone who needs greater time precision will want DATETIME2. What if you don’t need the precision? Most of us don’t even need milliseconds. So you can specify DATETIME2(0) which will only take 6 bytes to store and has a precision of seconds. If you want to store the exact same value you had in DATETIME, just choose DATETIME2(3), you get the same precision but it only takes 7 bytes to store the value instead of 8. I know a lot of you are thinking, what’s one byte, memory is cheap. But it’s not a question of how much space you have on your disk. When you are performance tuning, you want to store as many rows on a page as you can for each table and index. that means less pages to read for a table or query, and more rows you can store in cache. Many of our tables have multiple date columns, and millions of rows. That one byte savings for every date value in your database is not going to make your users go ‘Wow everything is so much faster now’, but every little bit helps.
If you are building any brand new tables in SQL Server 2008, I recommend staying away from DATETIME and DATETIME2 altogether. Instead go for DATE and TIME. Yes, one of my happiest moments when I first started learning about SQL Server 2008 was discovering I could store the DATE without the time!! How many times have you used GETDATE() to populate a date column and then had problems trying to find all the records entered on ‘05-JUN-06’ and got no results back because of the time component. We end up truncating the time element before we store it, or when we query the date to ignore the time component. Now we can store a date in a column of datatype DATE. If you do want to store the time, store that in a separate column of datatype TIME. By storing the date and time in separate columns you can search by date or time, and you can index by date and or time as well! This will allow you to do much faster searches for time ranges.
Since we are talking about the date and time datatypes, I should also mention that there is another date datatype called DATETIMEOFFSET that is time zone aware. But that is a blog for another day if you are interested.
Here is a quick comparison of the different Date and Time Data types,
| Datatype | Range | Precision | Nbr Bytes | User Specified Precision |
| SMALL DATETIME | 1900-01-01 to 2079-06-06 | 1 minute | 4 | No |
| DATETIME | 1753-01-01 to 9999-12-31 | .00333 seconds | 8 | No |
| DATETIME2 | 0001-01-01 to 9999-12-31 23:59.59.9999999 | 100 ns | 6-8 | Yes |
| DATE | 0001-01-01 to 9999-12-31 | 1 day | 3 | No |
| TIME | 00:00:00.0000000 to 23:59.59.9999999 | 100 ns | 3-5 | Yes |
| DATETIMEOFFSET | 0001-01-01 to 9999-12-31 23:59.59.9999999 | 100 ns | 8-10 | Yes |
Today’s My 5 is of course related to the Date and Time datatypes.
My 5 Important Date functions and their forward and backwards compatibility
- GETDATE() – Time to STOP using GETDATE(), it still works in SQL Server 2008, but it only returns a precision of milliseconds because it was developed for the DATETIME datatype.
- SYSDATETIME() – Time to START using SYSDATETIME(), it returns a precision of nanoseconds because it was developed for the DATETIME2 datatype and it also works for populating DATETIME columns.
- DATEDIFF() – This is a great little function that returns the number of minutes, hours, days, weeks, months, or years between two dates, it supports the new date datatypes.
- ISDATE() – This function is used to validate DATETIME values. It returns a 1 if you pass it a character string containing a valid date. However if you pass it a character string representing a datetime that has a precision greater than milliseconds it will consider this an invalid date and will return a 0.
- DATEPART() – This popular function returns a portion of a date, for example you can return the year, month, day, or hour. This date function supports all the new date datatypes.
Also one extra note, because I know there are some former Oracle developers who use this trick. If you have any select statements where you select OrderDate+1 to add one day to the date, that will not work with the new date and time datatypes. So you need to use the DATEADD() function.
Comments
- Anonymous
June 22, 2011
The precision of DATETIME is actually 0.003 seconds, not 0.001 - Anonymous
June 23, 2011
Nice idea to split, we do a lot of grouping by date which would be faster if we had a date only column instead of datetime.However, is there even the most smallest chance that when creating a new row, (with the date and time fields being auto-generated when the row is created), that the date and time could be on different days, if it inserts at midnight (even if it is a one in one billion chance!)? - Anonymous
June 23, 2011
@Filip you are correct, I have updated the table to reflect the correct precision - Anonymous
June 23, 2011
@FinHi FinInteresting question. I created the following table using default values of SYSDATETIME for columns of type DATE, TIME, and DATETIME2.CREATE TABLE TimingIsEverything(id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,dateentered DATE DEFAULT SYSDATETIME() NULL,timeentered TIME DEFAULT SYSDATETIME() NULL,DateTimeentered DATETIME2 DEFAULT SYSDATETIME() NULL,description VARCHAR(50) NULL)Then I inserted three rows into the tableINSERT INTO timingiseverything(description) VALUES('Hello'),('so what '),('happens')With the nanosecond precision for SYSDATETIME I was curious to see if there would be a difference in the values across columns and rows.Here are the resultsid dateentered timeentered DateTimeentered description-- ------------ ---------------- --------------------------- --------------1 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 Hello2 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 so what3 2011-06-23 13:16:29.7471443 2011-06-23 13:16:29.7471443 happensSo as you can see the times did not change even when stored at the nanosecond level. I would be curious to try this on a table that had multiple indexes to be updated and a million rows that were fragmented, just to see if I could get a difference before stating unequivocally they will always be the same. I will see if I can do a little digging and get an official/definitive answer one way or another. - Anonymous
June 23, 2011
@Fin, because you list SYSDATETIME() twice it is likely called twice so I think the only way to be absolutely sure that you are getting a DATE and TIME that are from the exact same point in time is to use a program or STORED PROCEDURE. Call SYSDATETIME or an equivalent function to get the current date and time, store it in a variable, and pass that variable to the table to populate the columns. That way SYSDATETIME() is only called once and is guaranteed to have the same value for both columnssomething like thisCREATE PROCEDURE AddNewTimingRecord@Description VARCHAR(50)ASDECLARE @TodaysDate DATETIME2 = SYSDATETIME()INSERT INTO timingIsEverything(dateEntered, TimeEntered, Description)VALUES(@TodaysDate, @TodaysDate, @Description) - Anonymous
June 24, 2011
Thanks Susan, yeah stored procedures would solve the problem. I was just wondering as my typical setup would be using Linq to Entities to insert into the database, with my date field having a default of GETDATE(). For all practical purposes its not really an issue anyway! - Anonymous
June 25, 2011
@Fin, Susan,This does not help you answer the question but I think it would be a bug in SQL Server if the times were different because the INSERT should be a set-based operation. Therefore the data to be inserted should be determined as a snapshot at a single point in time. I.e. even though effectively a cursor may be used internally to produce the result (making you think the times could be different), the outcome should be as if the full INSERT happened instantaneously. - Anonymous
June 26, 2011
One more little gotta. If you're using an "older" version of Office (2007) as a front-end (Access in particular), it doesn't recognize the new data types. (Haven't checked Office 2010 yet). This is a problem when using date/time functions within the Office products. - Anonymous
June 27, 2011
Steve,You bring up an excellent point in general to consider whenever you are looking at new datatype: Client side support! You absolutely need to consider the client tools and code that will be accessing the database to make sure they support a datatype before implementing it. - Anonymous
June 29, 2011
I'm confused; your prose sys that DATETIME has a precision of 0.001 seconds, but then your chart shows a precision of 0.00333 seconds. Which is correct? - Anonymous
June 30, 2011
Hey Tarzan 0.00333 is correct, I had a mistake in the original blog and corrected it in the chart, but forgot it was mentioned in the text, sorry for the confusion. The chart is correct! - Anonymous
July 10, 2011
The comment has been removed - Anonymous
September 17, 2014
Hi SusanI want a seperate column of datatype date only bt in the visual studio 2010 only the Datetime data type is there...Date is not there...how can i solve this??? - Anonymous
September 17, 2014
Hi Khushiunfortunately you will often face the challenge of having different datatypes available on the client side and the back end. The DATE datatype is datatype in SQL Server. Not all client side languages will have have an equivalent datatype. You could store it in a datetime on the client and make sure the time is midnight, or move it to a string (but then you would lose the ability to use the functionality of the datetime datatype) - sorry I don't have an easy answer! Good luck! - Anonymous
December 22, 2014
Thank you. Very helpful. - Anonymous
February 02, 2015
Heh... for most applications, even .0033 seconds is huge resolution overkill. But, let's say that was the resolution that we were provided for DATETIME2 as @StartDT and @EndDate and we were tasked with calculating the duration between the two using at least a .0033 second resolution (just as an example).Here's the test code.DECLARE @StartDT DATETIME2(3) ,@EndDT DATETIME2(3);SELECT @StartDT = '1999-12-31 23:59:59.997' ,@EndDT = '2001-01-01 00:00:00.003';Using those dates and times, you write the code to display the duration in the hhhh:mi:ss:mil format and lets see how complicated it gets. Post that code and then I'll post the code that does the same thing using DATETIME.