Post reply

Smalldatetime

  • October 7, 2017 at 12:22 pm

    #77187

    Comments posted to this topic are about the item Smalldatetime

  • October 8, 2017 at 3:53 am

    #1962751

    Nice easy one to start the week, thanks Steve

    ...

  • October 9, 2017 at 11:59 am

    #1962874

    Well not my worry: check the avatar.  😉

    Thanks, Steve!

  • October 9, 2017 at 4:18 pm

    #1962913

    Nice one Steve! We actually have some systems with default datetime columns for end dates of 2076 and 2079. So this is actually a good call out for us if anyone opts to build a related table using smalldatetime columns instead.

  • October 10, 2017 at 1:16 am

    #1962948

    Nice one, thanks Steve
    even though Microsoft themselves recommend using the new date/time formats :

    Note

    Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • October 10, 2017 at 1:34 am

    #1962950

    Thanks, and it prompted me to revise the new date formats

  • October 10, 2017 at 5:27 am

    #1962975

    We've actually run into this when someone created a staging table with the smalldatetime datatype.  They were trying to save space, but ended up creating headaches for themselves.  Let's see here...it's a staging table!

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • October 10, 2017 at 6:44 pm

    #1963133

    Stewart "Arturius" Campbell - Tuesday, October 10, 2017 1:16 AM

    Nice one, thanks Steve
    even though Microsoft themselves recommend using the new date/time formats :

    Note

    Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

    Good point.

    I guess some people don't think their databases and asociated code will last beyond 2079 just as in the late 50s and onwards until some time in the 90s a lot of people didn't think their code or the data it created would last beyond 1999.   The fuss and panic that caused in the late 90s is not something that today's bright young things experienced, so it's a good thing that MS is reccomending the newer formats because a repeat of that problem with vastly larger databases may well have a bigger impact than the 2000 deadline did.  I think that using smalldatetime should be done only when there is a clear plan either to scrap the code and the data before 2079 or to rebuild the code and convert tha data to use a longer lasting type some time well before 2079 so that there's no last minute rush/panic - probably by 35 years before 2079 there will be no-one working in computing/IT who remembers the year 2000 issue and the associated panic, so probably for all data planned to last beyoned 2079 there should be a plan to convert to a newer datetime format before 2044, while there are still people around who won't be tempted to let it slide until too late.  I think it would be a good idea for Microsoft would deprecate the smalldatetime format early enough to effectively force people into converting by about then (ie deprecate it early enough that it no longer exists in any currently supported edition by well before 2079).   That way my grandchidren won't be caught in a repeat of the year 2000 date format panic.

    Tom

  • October 11, 2017 at 9:10 am

    #1963203

    Stewart "Arturius" Campbell - Tuesday, October 10, 2017 1:16 AM

    Nice one, thanks Steve
    even though Microsoft themselves recommend using the new date/time formats :

    Note

    Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

    Personally, I think the "new" DATETIME2, DATE, and TIME datatypes are seriously broken.  It destroyed the ability to do direct datetime math like DATETIME does.  And, yes, it's an ANSI requirement the EndDate-StartDate = Period and StartDate+Period = EndDate so MS took a giant leap backwards.  They apparently realized their mistake but, instead of fixing the datatype, they introduced DATEDIFF_BIG().

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

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