Why You Should Never Use DATETIME Again!

Why You Should Never Use DATETIME Again!

  • Comments 15

690px-Microsoft_SQL_Server_Logo.svgDates, 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

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • The precision of DATETIME is actually 0.003 seconds, not 0.001

  • 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!)?

  • @Filip you are correct, I have updated the table to reflect the correct precision

  • @Fin

    Hi Fin

    Interesting 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 table

    INSERT 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 results

    id dateentered  timeentered       DateTimeentered               description

    -- ------------ ----------------  ---------------------------   --------------

    1  2011-06-23 13:16:29.7471443  2011-06-23 13:16:29.7471443 Hello

    2  2011-06-23 13:16:29.7471443  2011-06-23 13:16:29.7471443 so what

    3  2011-06-23 13:16:29.7471443  2011-06-23 13:16:29.7471443 happens

    So 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.

  • @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 columns

    something like this

    CREATE PROCEDURE AddNewTimingRecord

    @Description VARCHAR(50)

    AS

    DECLARE @TodaysDate DATETIME2 = SYSDATETIME()

    INSERT INTO timingIsEverything

    (dateEntered, TimeEntered, Description)

    VALUES

    (@TodaysDate, @TodaysDate, @Description)

  • 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!

  • @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.

  • 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.

  • 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.

  • 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?

  • 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!

  • OK, I did go consult with a few more SQL gurus on the question about whether two columns in the same table using a DEFAULT SYSDATETIME() would ever return different values. Here is what I heard back from someone who brought it up with a group of SQL masters.

    "I imagine they would have the same value, but this is not how I would implement this.  Instead, why not just create computed fields based on the datetime as in the below.  This guarantees that all of the fields are always in sync.  You can index on the computed fields or declare persisted for storage-tradeoff to performance, so I don’t see any advantage over using explicit columns versus computed the date/time derived fields."

    CREATE TABLE importantStuff

    (id INT IDENTITY(1,1) NOT NULL, description VARCHAR(30),

    dateTimeEntered DATETIME2 DEFAULT SYSDATETIME(),

    dateEntered AS CONVERT(DATE, DateTimeEntered),

    timeEntered AS CONVERT(TIME, DateTimeEntered)

    )

    Thanks for a great discussion!

  • Hi Susan

    I 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???

  • Hi Khushi

    unfortunately 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!

  • Thank you. Very helpful.

Page 1 of 1 (15 items)