Implicit conversion from datetime to datetime2

Question

Post reply

Implicit conversion from datetime to datetime2

Alessandro Mortola

SSC Eights!

Points: 907
More actions
September 29, 2021 at 12:00 am

#3932233

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

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

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

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22680

September 29, 2021 at 7:10 am

#3934548

Good catch!

Thanks.

blehnig SSCrazy Points: 2900 More actions · Answer 2

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'

Alessandro Mortola SSC Eights! Points: 907 More actions · Answer 3

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)

This reply was modified 3 years, 2 months ago by Alessandro Mortola.

George Vobr SSChampion Points: 10009 More actions · Answer 4

Thanks Alessandro for this question and an excellent explanation.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

Alessandro Mortola SSC Eights! Points: 907 More actions · Answer 6

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

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)

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 9

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?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)