Nuance of datetime data type in SQL Server

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the item Nuance of datetime data type in SQL Server

  • Tom Gillies

    SSCrazy

    Points: 2737

    Thanks for sharing this one Leo. It is definitely a "Nuance" but it could just as easily become a "Nuisance". I have dealt with hunting down data problems in the past I have not previously encountered this. I hope I don't see it in the wild.
    Just when we thought that the "year 2000 problem" had gone away, something like this reminds us that having a "beginning of time" can cause problems as well! 😉

    Tom Gillies
    LinkedIn Profile
    www.DuhallowGreyGeek.com[/url]

  • SQLian

    Old Hand

    Points: 320

    As pedantic as this is.....

    "I can see only one way to do this"

      IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0

    I suspect it may well be less efficient, but wouldn't this also work:

      IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0

  • Thomas Franz

    Hall of Fame

    Points: 3503

    You should use an INLINE TABLE VALUE fuction instead of a scalar function - this would save a lot of time when you import many datasets

    CREATE FUNCTION dbo.f_xxx(@datetimevar varchar(50))
    RETURNS TABLE AS RETURN
    SELECT CASE WHEN len (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0 THEN 1 ELSE 0 END as bitvar
    ;

    SELECT t.*, f.bitvar
      FROM tbl AS t
     CROSS APPLY dbo.f_xxx(t.datetimevar) AS f

    God is real, unless declared integer.

  • louie1487 78804

    Right there with Babe

    Points: 755

    I don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.

    Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    SQLian - Thursday, April 13, 2017 3:29 AM

    As pedantic as this is.....

    "I can see only one way to do this"

      IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0

    I suspect it may well be less efficient, but wouldn't this also work:

      IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0

    One way mean parsing. There are many ways to parse and your way will work as well

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Leo Peysakhovich - Thursday, April 13, 2017 6:10 AM

    SQLian - Thursday, April 13, 2017 3:29 AM

    As pedantic as this is.....

    "I can see only one way to do this"

      IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0

    I suspect it may well be less efficient, but wouldn't this also work:

      IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0

    One way mean parsing. There are many ways to parse and your way will work as well

    By the way it is not working because both dates return 0 but the first date is correct while the second one presented bad data.

    select cast(cast(cast('1900 10:00' as date) as datetime) as int)

    select cast(cast(cast('10:00' as date) as datetime) as int)

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    louie1487 78804 - Thursday, April 13, 2017 5:22 AM

    I don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.

    Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.

    Datetime2 has the same behavior.

    declare @dt datetime2 = '10:00', @dt1 datetime2 = '1/1/1900'select @dt, @dt1;
    But the main point of the article that you accidently may have result while data is bad and this bad data will be in system unnoticed.

  • sknox

    SSChampion

    Points: 12191

    louie1487 78804 - Thursday, April 13, 2017 5:22 AM

    I don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.

    Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.

    It's wrong because assuming a value not provided is wrong. One common use-case where this makes datetime data types not fit for purpose is in genealogy.

    For example, if I were to tell you that I was born in 1973, would you assume that I was born on January 1, 1973? What if I were to say I was born some time in the 1970s? Does assuming 1970 exactly make sense? Yet it's common in genealogy to know someone's birth only as far as the decade. Sometimes you even know the month and day, but not the exact year.

    This is a side-effect of datetime data being stored atomically but represented non-atomically. Because it's stored atomically, it can only either be NULL or have a single value. But it's represented as six different intervals, each of which in real world situations may or may not be known.

  • JustMarie

    SSCertifiable

    Points: 7679

    I'm keeping this one in my briefcase because I'm dealing with a lot of legacy data out of multiple systems. I keep finding dirty data all over the place and have no doubt I'll find this one too.

    Thanks for sparing us some pain!

  • Luis Cazares

    SSC Guru

    Points: 183499

    SQLian - Thursday, April 13, 2017 3:29 AM

    As pedantic as this is.....

    "I can see only one way to do this"

      IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0

    I suspect it may well be less efficient, but wouldn't this also work:

      IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0

    You can reduce it even more.
    IF cast(@datetimevar as date) = '1900'

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • GeorgeCopeland

    SSCertifiable

    Points: 6749

    Oh good grief. Whenever I hear people talking about this subject like this, it drives me absolutely *nuts*. I want to get out a hammer and start breaking fingers.

    The premise of the article is utterly wrong. This is not a nuance, it is flat NORMAL. It is not something uncommon, it happens *everywhere*.

    It is a fundamental lack of comprehension of the datetime datatype. Thinking like this has caused more bugs over the last 70 years than the NULL. It is thinking like this that gave the world Y2K.

    Please get this through your thick heads--

    EVERY datetime has a date. EVERY datetime has a time.

    There are no exceptions to this, none.

    When you get date data in, immediately store it in a datetime and *leave it there*. Do not allow users to enter invalid dates, use a calendar control.

    If you convert a datetime to text for something other than display, you are doing something WRONG. By WRONG, I mean completely bat-droppings crazy.

    alter function dbo.udfTimeOnly ( @datetimevar  datetime )
      returns  bit
    as
    begin
      declare @bitvar bit;
      IF cast(@datetimevar as date) = cast(0 as datetime)
          --date is zero date--assume invalid
          set @bitvar =  1;
      ELSE
          set @bitvar =  0;
                              
     return @bitvar;           
    end;

    As a another ranting aside, to show foobar data, I highly advise using something other than three exes, you know, these things: x.

  • Mighty

    SSCrazy Eights

    Points: 8338

    sknox - Thursday, April 13, 2017 6:38 AM

    louie1487 78804 - Thursday, April 13, 2017 5:22 AM

    I don't agree at thinking that when a time value is inserted and the default is 1900-01-01 would be "wrong". You inserted a time into a type that is for date AND time. Isn't this why a time datatype exist? Maybe I missed the point in the discussion and that's on me.

    Also, why not switch to datetime2 and the default becomes a year you for sure know is not a valid date, unlike 1900-01-01 as you mentioned could be a real date in certain scenarios.

    It's wrong because assuming a value not provided is wrong. One common use-case where this makes datetime data types not fit for purpose is in genealogy.

    For example, if I were to tell you that I was born in 1973, would you assume that I was born on January 1, 1973? What if I were to say I was born some time in the 1970s? Does assuming 1970 exactly make sense? Yet it's common in genealogy to know someone's birth only as far as the decade. Sometimes you even know the month and day, but not the exact year.

    This is a side-effect of datetime data being stored atomically but represented non-atomically. Because it's stored atomically, it can only either be NULL or have a single value. But it's represented as six different intervals, each of which in real world situations may or may not be known.

    The datetime datatype is assuming a "default" when, in this case, only the time is provided. That's just the way it is, and according to me this makes sense. Otherwise you should only allow full date and time information (until the precision of the chosen datetime datatype) to be used to fill a datetime. Because otherwise you can also asked why '2017-04-14' is defaulted to '2017-04-14 00:00:000', even though it should be '2017-04-14 01:01:001'.
    You are correct is stating that a datetime is not fit for purpose in genealogy, so the use of a datetime there doesn't make any sense there. In a correct data model design you would use multiple fields (year, month, day,...) to store this information. 1973 is not a date, it is a year, so why would you even want to store it in a datetime column?

  • Jeff Moden

    SSC Guru

    Points: 993661

    SQLian - Thursday, April 13, 2017 3:29 AM

    As pedantic as this is.....

    "I can see only one way to do this"

      IF len  (ltrim(@datetimevar)) <= 8 and charindex(':',@datetimevar) > 0

    I suspect it may well be less efficient, but wouldn't this also work:

      IF cast(cast(cast(@datetimevar as date) as datetime) as int) = 0

    Not for the purpose intended in this case.  Leo's code is designed to check to see if the date is included in the original character based data.  If not, it's a problem.  If it is, even for the 1900-01-01 date (he did say he needed to allow for that), then it correctly passes the test using his code. It won't, in this case, for yours because you're rejecting the 1900-01-01 date.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993661

    Mighty - Friday, April 14, 2017 5:30 AM

    The datetime datatype is assuming a "default" when, in this case, only the time is provided. That's just the way it is, and according to me this makes sense.

    Heh... and just the way I like it.  I've worked on other systems that try to protect the programmer/user and, while the intention is good, it defeats building special purpose code that can't withstand the automatic protection.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 18 total)

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