Post reply

Fun with Dates and Times

  • January 16, 2019 at 7:02 am

    #2019075

    Sean Redmond - Wednesday, January 16, 2019 6:51 AM

    Jeff Moden - Wednesday, January 16, 2019 6:36 AM

    Actually, DATETIME is stored as two integers, the first being the number of days since the first of January, 1900 (day "0") and the second being the number of 1/300ths of a second since midnight (which is why the resolution of the datatype is 3.3ms).  The cool part about DATETIME (and SMALLDATETIME) is that they're built to allow direct date math where the newer types are not.

    This is SMALLDATETIME surely? DATETIME allows for dates that go all the way back to 1753.

    The first integer can store a negative number, thus reaching 1753.

  • January 16, 2019 at 7:05 am

    #2019077

    Jeff Moden - Wednesday, January 16, 2019 6:36 AM

    Actually, DATETIME is stored as two integers, the first being the number of days since the first of January, 1900 (day "0") and the second being the number of 1/300ths of a second since midnight (which is why the resolution of the datatype is 3.3ms).  The cool part about DATETIME (and SMALLDATETIME) is that they're built to allow direct date math where the newer types are not.

    I DO absolutely agree with you that the only time a temporal datatype should be formatted is for display purposes.

    Maybe so.  My point, perhaps poorly made, was that it's not stored as YYYY-MM-DD or anything like it.  Nobody does, except as a string.  It's stored numerically and is agnostic of the required display format, which is decided later.  A given piece of SQL may perhaps feed the date in as a string but a Datetime column (of whatever flavour) stores it as number(s).

  • January 16, 2019 at 7:10 am

    #2019078

    Jerry Kelk - Wednesday, January 16, 2019 7:05 AM

    Maybe so.  My point, perhaps poorly made, was that it's not stored as YYYY-MM-DD or anything like it.  Nobody does, except as a string.  It's stored numerically and is agnostic of the required display format, which is decided later.  A given piece of SQL may perhaps feed the date in as a string but a Datetime column (of whatever flavour) stores it as number(s).

    Totally agreed there.  Just shouldn't store formatted temporal data in a table unless it's a staging table being used for validation or to create an output file.

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

  • January 16, 2019 at 7:29 am

    #2019081

    This whole date thingy ought to be a lot simpler than all what this rhetoric makes it sound.
    Two of the main issues with dates for a computer is ordering date values chronologically and doing "date math".  The data should be stored in the database in a format that allows the machine to sort chronologically and perform date function calculations - which is what computers are for, with the behind-the-scenes database functionality available to re-format the data in any one of generally used readable formats for "readability" uses.
    Don't let the issue of "readability" muckup the need for chronological sorting and calculations.  Readability is used for human viewing purposes such as reports, etc., and is at the viewer's discretion.  Otherwise, who cares?
    Moral of the story - store the data in the database the way a computer would want it, and have the functionality available to convert it to any desired human consumption format when needed.
    And to prove my point, I will say this:  "the right way to do it is my way!"  Now let the fur fly....

  • January 16, 2019 at 7:43 am

    #2019085

    mark.edwards-1115881 - Wednesday, January 16, 2019 7:29 AM

    This whole date thingy ought to be a lot simpler than all what this rhetoric makes it sound.
    Two of the main issues with dates for a computer is ordering date values chronologically and doing "date math".  The data should be stored in the database in a format that allows the machine to sort chronologically and perform date function calculations - which is what computers are for, with the behind-the-scenes database functionality available to re-format the data in any one of generally used readable formats for "readability" uses.
    Don't let the issue of "readability" muckup the need for chronological sorting and calculations.  Readability is used for human viewing purposes such as reports, etc., and is at the viewer's discretion.  Otherwise, who cares?
    Moral of the story - store the data in the database the way a computer would want it, and have the functionality available to convert it to any desired human consumption format when needed.
    And to prove my point, I will say this:  "the right way to do it is my way!"  Now let the fur fly....

    Heh... you just say it doesn't "prove" you point.  😀  The point is that you need to keep the data layer and presentation layer separate even in SQL Server. 😉

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

  • January 16, 2019 at 7:46 am

    #2019086

    For database storage, I'd suggest DATETIME type populated with GETUTCDATE(). Each user can specify preferred time zone and display preference under our account profile, and then let the web application do the math and format the text as needed.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 16, 2019 at 8:07 am

    #2019089

    This article describes displaying dates, not storing date values in a database.  When a date is displayed in a text format, yyyyMMdd, with or without separators, is the format that is both sortable and immediately recognizable across cultures.

  • January 16, 2019 at 8:31 am

    #2019098

    If the article is about DISPLAYING dates and not STORING dates, then it should have said so, which it didn't.  You have to be familiar with the issues involving date storage and display in order to even remotely realize that is what it is (implicitly) talking about.

    So far, reading all the comments, it sounds like we all agree, store dates as date type, and if a string type, format it in a way that the data at least sorts chronologically if you need to sort it, otherwise display according to the need, and the best way to do that is whatever way you need to do it.

    My beef is with the current SQL Server formatting functions - cumbersome compared to other db's.

  • January 16, 2019 at 8:32 am

    #2019099

    I have been programming for over 40  years now and for the life of me I never understood why ANY human prefers numeric dates (due to the ambiguity between day and month values between 1 and 12); unless, for obvious reasons, the system being designed calls for such dates to be used.  I follow these rules:

    1. Store the dates in the most appropriate way given the system you are using, IE: My SQL vs MS SQL vs ORACLE -- all have specific date/time datatypes.

    2. FORMAT the date/time in HUMAN readable terms, the format I have always used (and in 40 years have NEVER had a complaint) is: dd-mmm-yyyy hh:mm tt , example: 16-Jan-2019 8:32 am.  For different cultures the month abbreviation is changed accordingly.

    Of course, if the specific report / job requires a specific date/time format - then so be it -- we all must obey whatever specs are handed to us.

  • January 16, 2019 at 8:48 am

    #2019103

    I use a lot of date formats. For me the most recent widely adapted format is:

    YYYY-MM-DD@HH-MM

    and

    YYYY-MM-DD@HH-MM-SS

    412-977-3526 call/text

  • January 16, 2019 at 9:08 am

    #2019110

    For displaying dates, or setting a date column or variable from a string literal, I've always preferred YYYY-MM-DD format.  It's unambiguous and sorts nicely.  I believe the format with DD-MON-YYYY such as 16-Jan-2019 is the Oracle default format for displaying dates, but I always found that awkward.

  • January 16, 2019 at 3:53 pm

    #2019163

    Steve Jones - SSC Editor wrote:

    Eric M Russell - Wednesday, January 16, 2019 7:00 AM[/b]

    Steve, I'm a little confused; are you talking about storing date/time strings in VARCHAR columns or just how the date/time is displayed at various points in the site?

    Display. These are stored as date or datetime2 data types.

  • January 16, 2019 at 6:49 pm

    #2019172

    bdcoder - Wednesday, January 16, 2019 8:32 AM

    I have been programming for over 40  years now and for the life of me I never understood why ANY human prefers numeric dates (due to the ambiguity between day and month values between 1 and 12); unless, for obvious reasons, the system being designed calls for such dates to be used.  I follow these rules:

    1. Store the dates in the most appropriate way given the system you are using, IE: My SQL vs MS SQL vs ORACLE -- all have specific date/time datatypes.

    2. FORMAT the date/time in HUMAN readable terms, the format I have always used (and in 40 years have NEVER had a complaint) is: dd-mmm-yyyy hh:mm tt , example: 16-Jan-2019 8:32 am.  For different cultures the month abbreviation is changed accordingly.

    Of course, if the specific report / job requires a specific date/time format - then so be it -- we all must obey whatever specs are handed to us.

    Same here.  My favorite format (and I use it when writing, as well) is the same as what you posted above without the dashes.  Depending on the intended audience, I use either the AM/PM format for the 24 hour format for the time.

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

  • January 17, 2019 at 8:22 am

    #2019213

    When it comes to pleasing the customer, it's best to keep the salt and pepper shaker on the table rather than have the chef make assumptions.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Viewing 14 posts - 16 through 28 (of 28 total)

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