Nuance of datetime data type in SQL Server

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

  • 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 Profilewww.DuhallowGreyGeek.com[/url]

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

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

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

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

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

  • 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!

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

  • 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?

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

    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)

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

    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)

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

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