Post reply

Is date zero valid and if so what day is it?

  • June 10, 2024 at 12:00 am

    #4423827

    Comments posted to this topic are about the item Is date zero valid and if so what day is it?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • June 10, 2024 at 4:05 pm

    #4427469

    Just to add to this (Great question, BTW!)...

    The zero is known as a "Date Serial" number.  DATETIME and SMALLDATETIME are capable of using Date Serial numbers directly whereas DATE, DATETIME2 (etc) are not.

    The "base date" of zero is also known as the "Epoch Date".  And, yes, there can be and are negative Date Serial numbers.

    Behind the scenes, datatypes such as DATE and DATETIME2 have an Epoch Date of 0001-01-01 but the default date for a lot of the functions is still 1900-01-01, which is the Epoch Date for DATETIME and SMALLDATETIME.

    It's a real shame that MS ignored the fantastic "direct date manipulation" characteristics that DATETIME and SMALLDATETIME have when they made the newer temporal datatypes like DATE and DATIME2.

    For example...

    https://www.sqlservercentral.com/articles/calculating-duration-using-datetime-start-and-end-dates-sql-spackle-2

    MS did partially realize their mistake and, apparently, tried to make up for it a bit by the late introduction of the DATEDIFF_BIG() function but failed to follow through because they still have yet to create and implement ad DATEADD_BIG() function.

     

    --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 2 posts - 1 through 1 (of 1 total)

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