Implicit conversion from datetime to datetime2

  • Comments posted to this topic are about the item Implicit conversion from datetime to datetime2

  • Good catch!

    Thanks.

  • This seems even a bit more complicated. For example:

    - compatibility level = 120

    - original datetime = '20210701 12:30:45.126'

    - implicit conversion I get back = '2021-07-01 12:30:45.1270000'

  • Hi blehnig,

    thanks for your comment. Keep in mind that for DateTime data type, the third digit after the decimal point can be only: 0, 3 or 7. Every other numer is rounded according to the following rule:

    0, 1 and 9 are cast into 0

    (More precisely, with 9 you will obtain 0 as last digit and the second to last digit incremented by 1. This is generally irrelevant but pay attention because this could cause a sort of 'chain reaction'. In fact, carrying out this statement select CAST('20210930 23:59:59.999' as datetime) you will obtain '2021-10-01 00:00:00.000' )

    2, 3 and 4 are cast into 3

    5, 6, 7 and 8 are cast into 7

    so in your example, the string  '20210701 12:30:45.126' generates the DateTime  20210701 12:30:45.127.

    Try this one for proof: select CAST('2021-07-01 12:30:45.126' as datetime)

    From here the result 2021-07-01 12:30:45.1270000 in dateTime2(7)

     

     

  • Thanks Alessandro for this question and an excellent explanation.

  • Ok... there's one I didn't know.  The trouble for me is that isn't an "improvement in accuracy" for me.  It's a 0.0003333 ERROR in ACCURACY to me.

    This is almost as bad as that bloody 6 decimal-place rounding junk that MS does with the DECIMAL and NUMERIC datatypes. 🙁

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I agree with you Jeff and with your "ERROR in ACCURACY" definition 🙂

  • I vote for NULL. We  really don't know that are the last 4 digits and unknown is NULL. We should not assume that the numbers will be 3333. As a data analyst, we don't assume what the values are, we accurately present the known values. In this situation I may put the number as is with 3 digits after the period  and  add a comment in the report that the 4th digit after the period is probably less than 5. Or put  a mean number 2500 as last 4 digits and make a comment that since the original number had  123 at the end and not 124, then  the real value for 4 other digits will be less then 5000, so statistical mean will be  2500 (some may argue 2499) and put a comment in the report about the assumptions.

    So my answers:

    either NULL

    or

    2021-07-01 12:30:45.1232500 ( with adding the above comment)

    Regards,Yelena Varsha

  • Alessandro Mortola wrote:

    I agree with you Jeff and with your "ERROR in ACCURACY" definition 🙂

    I have to thank you for the question!  It's also ironic that I actually had to do this conversion today and it was great that I knew the answer or I'd have been wondering what the heck went wrong.

    I actually DO understand why some call it an improvement in accuracy because the original accuracy of DATETIME is 3 and a third  milliseconds and so it's a natural extension but it's still not what was in the original.  It's almost as bad as rounding in the other direction.

    How the heck people have time to keep up on these "minutia" changes is amazing to me.  Again, thank you for the question.  It saved me a bit of sanity today.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • From my previous life as a production engineer (making stuff) I'd call it an improvement in resolution, rather than accuracy. Along the same lines as someone hamfisted using a vernier micrometer which has a resolution of microns to measure a roughly turned piece of soft copper. Yes they have a resolution of 0.001 mm available, but accuracy? More like 0.01.

    "Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
    — Samuel Johnson
    I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

  • david.edwards 76768 wrote:

    From my previous life as a production engineer (making stuff) I'd call it an improvement in resolution, rather than accuracy. Along the same lines as someone hamfisted using a vernier micrometer which has a resolution of microns to measure a roughly turned piece of soft copper. Yes they have a resolution of 0.001 mm available, but accuracy? More like 0.01.

    Heh... I resolve to agree with the accuracy of that description. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 11 (of 11 total)

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