SQL Server Datetime vs. Datetime2

  • hartmann 74688

    Old Hand

    Points: 346

    Comments posted to this topic are about the item SQL Server Datetime vs. Datetime2

  • Eirikur Eiriksson

    SSC Guru

    Points: 182523

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the refresher.

  • fvinson

    SSC Enthusiast

    Points: 145

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

  • pjdijkstra

    SSC Enthusiast

    Points: 199

    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.

  • hartmann 74688

    Old Hand

    Points: 346

    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.

  • Phil Parkin

    SSC Guru

    Points: 244784

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • hartmann 74688

    Old Hand

    Points: 346

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

  • Phil Parkin

    SSC Guru

    Points: 244784

    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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin

    SSC Guru

    Points: 244784

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Nadrek

    SSC-Insane

    Points: 20039

    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)

  • chris 24158

    SSC Enthusiast

    Points: 144

    There are additional considerations for .net developers. See

    http://stackoverflow.com/questions/1334143/sql-server-datetime2-vs-datetime

    http://stackoverflow.com/questions/8421332/for-the-net-datetime-type-why-is-the-inferred-database-type-sqldbtypes-datetim

    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.

  • jpratt-797544

    SSC Eights!

    Points: 847

    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

  • Phil Parkin

    SSC Guru

    Points: 244784

    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))

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • GeorgeMandler

    Grasshopper

    Points: 15

    "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 21 total)

You must be logged in to reply to this topic. Login to reply