January 5, 2016 at 12:00 am
Comments posted to this topic are about the item SQL Server Datetime vs. Datetime2
January 5, 2016 at 1:49 am
Thank you Robert for this excellent piece, good job and a very good reference indeed.
😎
The greatest disadvantages I find with the DATETIME2 data type are firstly the default precicion of 7, simply a waste of space in most cases and secondly the fact that operations / functions on DATETIME2 are up to 50% slower than what is possible with the DATETIME datatype. (example here).
January 5, 2016 at 6:26 am
Thanks for the refresher.
January 5, 2016 at 6:42 am
This is a trivial typo, but here it is anyway: I believe that the statement "the datetime type is not valid before 1763" should say "before 1753" (because of the calendar change in 1952).
January 5, 2016 at 7:11 am
We went back from Datetime2 to Datetime because when we saved the data to excel (from SSRS) Excel wouldn't interpret the Datetime2 fields as dates. We didn't want to always cast to Datetime in our SSRS reports.
SQL Standard means a lot less to our end-users than Excel standard.
January 5, 2016 at 7:59 am
pjdijkstra (1/5/2016)
We went back from Datetime2 to Datetime because when we saved the data to excel (from SSRS) Excel wouldn't interpret the Datetime2 fields as dates. We didn't want to always cast to Datetime in our SSRS reports.SQL Standard means a lot less to our end-users than Excel standard.
Well the data should nevertheless be stored in datetime2. For that purpose you can create a provisioning layer via VIEWS.
Then you have the benefit of a "better" storage type in backend but still compatible types for excel users.
Beside that i know you cannot always apply the newest data types because of historical processes that cannot be updated.
January 5, 2016 at 8:10 am
hartmann 74688 (1/5/2016)
pjdijkstra (1/5/2016)
We went back from Datetime2 to Datetime because when we saved the data to excel (from SSRS) Excel wouldn't interpret the Datetime2 fields as dates. We didn't want to always cast to Datetime in our SSRS reports.SQL Standard means a lot less to our end-users than Excel standard.
Well the data should nevertheless be stored in datetime2. For that purpose you can create a provisioning layer via VIEWS.
Then you have the benefit of a "better" storage type in backend but still compatible types for excel users.
Beside that i know you cannot always apply the newest data types because of historical processes that cannot be updated.
When you make absolute statements such as this, please justify them.
Personally, I will continue to use datetime unless I need the additional accuracy afforded by datetime2. Why? Because datetime columns perform better than datetime2 columns.
--Edited to remove disk-space comment inaccuracy.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 5, 2016 at 8:16 am
Personally, I will continue to use datetime unless I need the additional accuracy afforded by datetime2. Why? Because datetime columns perform better and require less disk space than datetime2 columns.
Well that is kind of the point of my article that datetime does not require less disk space. datetime requires 8 bytes and datetime2 requires UP to 8 bytes (ranging from 6 to 8 bytes). So it also depends if you come from a 100 GB Data warehouse or a 10 TB Data warehouse. Those 2 KB can make an impact.
But i understand people keep using datetime, no problems. But your statement regarding disk space is not true.
In the end it depends of personal flavor, amount of data and such things like the guy mentioned above me i.e. compatibility with you application (e.g. excel).
January 5, 2016 at 8:18 am
I spotted a few minor typos:
"legacy tables that uses datetime" - use
"some developer simply" - developers
"8 byte of storage" - bytes
"ambigous" - ambiguous
"Let;s " - Let's
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 5, 2016 at 8:19 am
hartmann 74688 (1/5/2016)
Personally, I will continue to use datetime unless I need the additional accuracy afforded by datetime2. Why? Because datetime columns perform better and require less disk space than datetime2 columns.
Well that is kind of the point of my article that datetime does not require less disk space. datetime requires 8 bytes and datetime2 requires UP to 8 bytes (ranging from 6 to 8 bytes). So it also depends if you come from a 100 GB Data warehouse or a 10 TB Data warehouse. Those 2 KB can make an impact.
But i understand people keep using datetime, no problems. But your statement regarding disk space is not true.
In the end it depends of personal flavor, amount of data and such things like the guy mentioned above me i.e. compatibility with you application (e.g. excel).
Absolutely right. I will edit my original post accordingly.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 5, 2016 at 8:37 am
Personally, I either convert from DATETIME to DATE (reduce storage, and to the day is actually MORE accurate per the business requirements), to DATETIME2(2) (reduce storage to 6 bytes with hundredth of a second precision), or to DATETIME2(7) (same 8 bytes of storage, much better precision), depending on the business requirements.
I have yet to go anywhere in the middle - for my work, either it needs precision substantially greater than a hundredth of a second, or a hundredth of a second is more than enough, or it was already a SMALLDATETIME, or the business requirement operates on days instead of hours, minutes, or seconds anyway (When did you turn 18? On your birthday; at the day level, not at the precise time you were actually listed as having been born)
January 5, 2016 at 10:27 am
There are additional considerations for .net developers. See
http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime
Specifically, .net assumes datetime when adding a sqlParameter, and known legacy issues due to odbc style drivers and anything prior to .net 3.5 not knowing about datetime2.
January 5, 2016 at 10:59 am
I've been burned in the past with the .997 accuracy of datetime, especially when dealing with orders that come in or events that occur during just before midnight. An example WHERE clause criterion looks like this: AND (t.ticket_date BETWEEN @SearchFromTicketDate AND @SearchToTicketDate + ' 23:59:59:997'). Using ":999" doesn't work because anything logged during ":998" or ":999" rolls to the next hour (":000") with datetime, which when occurring right before midnight rolls the date to the next day. And no, I don't have terabytes of data, so this WHERE criterion performs very well with several hundred thousand rows and proper indexing.
I have recently started converting all create date and last modified date values to datetime2(7), as well as any other event/logging dates (ticket dates, order dates, etc.) that require great precision. When I don't need a time value I use date. I will still use the original datetime IF I need a time value with the date AND I don't worry about the .997 accuracy (an example would be a meeting start/end date and time value).
J Pratt
January 5, 2016 at 11:24 am
This construct
t.ticket_date BETWEEN @SearchFromTicketDate AND @SearchToTicketDate + ' 23:59:59:997')
Should be written like this, to completely avoid any problems with fractions:
t.ticket_date >= @SearchFromTicketDate AND t.ticket_date < (@SearchToTicketDate + 1 day))
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 5, 2016 at 1:52 pm
"So datetime is stored as little endian meaning the most significant byte is on the leftmost while in big endian the most significant byte is stored on the rightmost position."
The opposite is true:
Little endian - the least significant byte is on the leftmost position :w00t:
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply