Date fun

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    Comments posted to this topic are about the item Date fun

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Nice question, thanks Steve, have used similar methods to calculate e.g. first and last days of the month, etc

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

  • Carlo Romagnano

    SSC-Insane

    Points: 22008

    Ahahaha, good fun!

  • HappyGeek

    SSCoach

    Points: 18684

    Nice one thanks Steve.

    ...

  • Nick Ryan

    SSCrazy

    Points: 2237

    Ouch my head. I really, really hate the date hacks that are required in MS-SQL for otherwise relatively simple (in concept) date manipulations. I do have cause to use quite a few of them though...

  • allinadazework

    SSCarpal Tunnel

    Points: 4365

    Nice question, thanks.

  • sipas

    Hall of Fame

    Points: 3284

    American dates are weird.

  • Luis Cazares

    SSC Guru

    Points: 183637

    sipas - Tuesday, October 3, 2017 5:51 AM

    American dates are weird.

    American dates?

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

    Hall of Fame

    Points: 3284

    Luis Cazares - Tuesday, October 3, 2017 5:58 AM

    sipas - Tuesday, October 3, 2017 5:51 AM

    American dates are weird.

    American dates?

    UK dates are ddmmyyyy (or yyyymmdd) as opposed to mmddyyyy (or yyyyddmm).
    This makes it easy (for example) to sort dated files in order by using the file name if it has a yyyymmdd suffix.
    NB For the avoidance of doubt, it wasn't a serious comment, I'm sure everyone else thinks UK dates are weird.

  • Nick Ryan

    SSCrazy

    Points: 2237

    Interesting comments about dates... although if you think about it - all dates are weird.

    What is important to consider though is that for an international website it is important to always represent dates in a commonly understood and interpreted format. In this case the ISO date format is correct and this is what was shown in the example. If the example used the US (or any other regional specific date format) then it would be incorrect. MS-SQL has clearly defined ways of interpreting dates that are presented in ISO format and dates that are not presented in an ISO format will be handled at the whim of whatever local date format is in operation at the time and this may not be, and often is not, what is expected. Consider 3/11 or 11/3 - is this the 3rd day of the 11th month or the 11th day of the 3rd month?

    The issue with US format dates is that the day and month are swapped compared to much of the rest of the world. While neither technically correct or incorrect (actually, dammit, I'm from the UK: it is incorrect and please drive on the correct side of the road as well.. πŸ˜‰ ) it is interesting in that it often matches how we speak a date rather than how we write a date: do you say "the 1st of March" or "March the 1st" (technically "March, the 1st")? Neither is more wrong or right than the other.

    All of this is fine when you consider just the Gregorian calendar - other calendars are available.

  • Jeff Moden

    SSC Guru

    Points: 997104

    sipas - Tuesday, October 3, 2017 5:51 AM

    American dates are weird.

    Heh... and we're damned proud of it. πŸ˜‰

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 997104

    Considering the correct answer, my question would be, what are the Use Cases behind doing such a thing?

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Luis Cazares

    SSC Guru

    Points: 183637

    sipas - Tuesday, October 3, 2017 6:13 AM

    Luis Cazares - Tuesday, October 3, 2017 5:58 AM

    sipas - Tuesday, October 3, 2017 5:51 AM

    American dates are weird.

    American dates?

    UK dates are ddmmyyyy (or yyyymmdd) as opposed to mmddyyyy (or yyyyddmm).
    This makes it easy (for example) to sort dated files in order by using the file name if it has a yyyymmdd suffix.
    NB For the avoidance of doubt, it wasn't a serious comment, I'm sure everyone else thinks UK dates are weird.

    Actually, UK and most of the world use dd/mm/yyyy, while the US uses mm/dd/yyyy. The format might change by using short names for the months or a different separator. The format used in the question is ISO yyyymmdd and should never be interpreted as something different.
    American dates are indeed the worst way to write a date, but there was no such example in the question.
    So yeah, people from the US don't know how to write dates properly and people from the UK don't drive on the right side of the road (pun intended).πŸ˜€

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

    Hall of Fame

    Points: 3284

    OK, I am even thicker (US dumber) than I thought! I didn't look at the question properly, worked out the difference between the dates as if they were American format (-2), added -2 to January 3rd and got January 1st 22:22 - the correct answer!
    It's such a fine line between stupid, and uh... Clever.
    Still got a point though, which is the Most Important Thing.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720887

    sipas - Tuesday, October 3, 2017 5:51 AM

    American dates are weird.

    That's why they weren't in the question πŸ˜‰

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

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