Interesting Datenames

  • chgn01 - Tuesday, November 14, 2017 10:23 AM

    run this script in sql2012 or sql2016, first time it return 2; run 2nd time it return 10.

    +1

    ...

  • carl.eaves - Tuesday, November 14, 2017 2:44 AM

    Got it right, but only because I deliberately put the wrong answer.
    There are people on here who live and work outside the US with their illogical date format.

    Actually I think MM/DD/YYYY and DD/MM/YYYY are both illogical.  I prefer YYYY-MM-DD, but most people roll their eyes at me for that.

  • Chris Harshman - Tuesday, November 14, 2017 12:39 PM

    carl.eaves - Tuesday, November 14, 2017 2:44 AM

    Got it right, but only because I deliberately put the wrong answer.
    There are people on here who live and work outside the US with their illogical date format.

    Actually I think MM/DD/YYYY and DD/MM/YYYY are both illogical.  I prefer YYYY-MM-DD, but most people roll their eyes at me for that.

    What I hate is people who post about palindrome dates such as 8-10-18 and insist on tagging me.  I have to tell them that I always write years with four digits ever since I was involved in fixing the Y2K bug, and that I almost always write my dates as yyyy-mm-dd, so the next palindrome date will be 2020-02-02.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jason A. Long - Tuesday, November 14, 2017 10:23 AM

    It's a trick question... The answer will change between the 1st and 2nd execution

    Why does that make it a trick question? Obviously you just run the code once, or it would tell you to run it again.
    It's no more a trick than is any question which creates but doesn't drop a table - if you run it twice then it will fail as the table already exists.

  • NBSteve - Tuesday, November 14, 2017 10:21 AM

    It feels inconsistent because it isn't quite accurate.  SET LANGUAGE doesn't change the value of the date, it changes the way string-date conversions are handled.  So when "DECLARE @RunDate DATE = '10/02/17' " is executed, it's the SET LANGUAGE value at that time that determines how it's interpreted.  Subsequent SET LANGUAGE calls will change how future string-date conversions are handled, even within the existing batch, but because @RunDate is already stored as a date, there are no more conversions being done here.  Compare the results of the above code to this, where an implicit string-date conversion happens in each line:  

    SET LANGUAGE Italian
    SELECT DATENAME(dd, '10/02/17')
    SELECT DATENAME(MONTH, '10/02/17')
    SET LANGUAGE US_English
    SELECT DATENAME(dd, '10/02/17')
    SELECT DATENAME(MONTH, '10/02/17')

    I completely overlooked that the date conversion was already done before. The "The SET LANGUAGE for the SELECT doesn't change this until the end of the batch." put me on a wrong track and made me believe that the change of the date format was done only at the end of the batch. It is in fact wrong, because the data format is directly changed when the SET LANGUAGE is called. Reading back the explanation I guess that the question had two SET LANGUAGE commands in there, because it is talking about "The SET LANGUAGE for the variable declaration" and that is not there.

  • Chris Harshman - Tuesday, November 14, 2017 12:39 PM

    carl.eaves - Tuesday, November 14, 2017 2:44 AM

    Got it right, but only because I deliberately put the wrong answer.
    There are people on here who live and work outside the US with their illogical date format.

    Actually I think MM/DD/YYYY and DD/MM/YYYY are both illogical.  I prefer YYYY-MM-DD, but most people roll their eyes at me for that.

    This

  • Steve Jones - SSC Editor - Wednesday, November 15, 2017 9:02 AM

    Chris Harshman - Tuesday, November 14, 2017 12:39 PM

    carl.eaves - Tuesday, November 14, 2017 2:44 AM

    Got it right, but only because I deliberately put the wrong answer.
    There are people on here who live and work outside the US with their illogical date format.

    Actually I think MM/DD/YYYY and DD/MM/YYYY are both illogical.  I prefer YYYY-MM-DD, but most people roll their eyes at me for that.

    This

    Agreed.  All the country-specific formats are just that - formats.  The YYYY-MM-DD is universal and understandable by everyone.  As a bonus, they sort nicely in Windows Explorer.

  • Ed Wagner - Wednesday, November 15, 2017 9:22 AM

    Agreed.  All the country-specific formats are just that - formats.  The YYYY-MM-DD is universal and understandable by everyone.  As a bonus, they sort nicely in Windows Explorer.

    I would not say universal, but at least it is the ODBC standard time format. And who never used ODBC?! 😀

  • Mighty - Wednesday, November 15, 2017 11:27 AM

    Ed Wagner - Wednesday, November 15, 2017 9:22 AM

    Agreed.  All the country-specific formats are just that - formats.  The YYYY-MM-DD is universal and understandable by everyone.  As a bonus, they sort nicely in Windows Explorer.

    I would not say universal, but at least it is the ODBC standard time format. And who never used ODBC?! 😀

    Ah... be careful now.  That format actually isn't the proper universal ANSI format.  YYYYMMDD is the proper format.  Further, the format you have above is subject to the same problems as most date formats and IS language sensitive.  If you don't think so, then try this and wonder why I jumped Celko's bones for saying the YYYY-MM-DD format was the preferred format.


     SET LANGUAGE US_English;
     SELECT CONVERT(DATETIME, '2017-02-04');

     SET LANGUAGE Italian;
     SELECT CONVERT(DATETIME, '2017-02-04');

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden - Wednesday, November 15, 2017 11:50 AM

    ... I jumped Celko's bones ...

  • Jeff Moden - Wednesday, November 15, 2017 11:50 AM

    Mighty - Wednesday, November 15, 2017 11:27 AM

    Ed Wagner - Wednesday, November 15, 2017 9:22 AM

    Agreed.  All the country-specific formats are just that - formats.  The YYYY-MM-DD is universal and understandable by everyone.  As a bonus, they sort nicely in Windows Explorer.

    I would not say universal, but at least it is the ODBC standard time format. And who never used ODBC?! 😀

    Ah... be careful now.  That format actually isn't the proper universal ANSI format.  YYYYMMDD is the proper format.  Further, the format you have above is subject to the same problems as most date formats and IS language sensitive.  If you don't think so, then try this and wonder why I jumped Celko's bones for saying the YYYY-MM-DD format was the preferred format.


     SET LANGUAGE US_English;
     SELECT CONVERT(DATETIME, '2017-02-04');

     SET LANGUAGE Italian;
     SELECT CONVERT(DATETIME, '2017-02-04');

    well, it's not JC's or our fault that Microsoft can't figure out there isn't any such format as YYYY-DD-MM 😛
    the ISO standard is either YYYY-MM-DD or YYYYMMDD:  https://en.wikipedia.org/wiki/ISO_8601

  • Chris Harshman - Wednesday, November 15, 2017 2:13 PM

    Jeff Moden - Wednesday, November 15, 2017 11:50 AM

    Mighty - Wednesday, November 15, 2017 11:27 AM

    Ed Wagner - Wednesday, November 15, 2017 9:22 AM

    Agreed.  All the country-specific formats are just that - formats.  The YYYY-MM-DD is universal and understandable by everyone.  As a bonus, they sort nicely in Windows Explorer.

    I would not say universal, but at least it is the ODBC standard time format. And who never used ODBC?! 😀

    Ah... be careful now.  That format actually isn't the proper universal ANSI format.  YYYYMMDD is the proper format.  Further, the format you have above is subject to the same problems as most date formats and IS language sensitive.  If you don't think so, then try this and wonder why I jumped Celko's bones for saying the YYYY-MM-DD format was the preferred format.


     SET LANGUAGE US_English;
     SELECT CONVERT(DATETIME, '2017-02-04');

     SET LANGUAGE Italian;
     SELECT CONVERT(DATETIME, '2017-02-04');

    well, it's not JC's or our fault that Microsoft can't figure out there isn't any such format as YYYY-DD-MM 😛
    the ISO standard is either YYYY-MM-DD or YYYYMMDD:  https://en.wikipedia.org/wiki/ISO_8601

    Heh... that's the fun part about standards... there are so many of them. :sick:  MS also took a giant leap backwards from ANSI standards by not allowing direct date math on DATE, TIME, and DATETIME2.

    Wikipedia is usually pretty good about correctly repeating what is in the standards but, even though is doesn't actually matter at this point, I'll double check the copy that I have when I get home tonight.

    --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)
    Intro to Tally Tables and Functions

  • Jason A. Long - Wednesday, November 15, 2017 1:27 PM

    Jeff Moden - Wednesday, November 15, 2017 11:50 AM

    ... I jumped Celko's bones ...

    Heh... understood. 😉

    --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)
    Intro to Tally Tables and Functions

  • Jeff Moden - Wednesday, November 15, 2017 3:20 PM

    Heh... understood. 😉

    Sorry... I never really matured, mentally, beyond the average 15 year old... It couldn't be helped...
    It's the same reason the Flash Gordon theme song pops into my head any time I see a Celko post... (and now I'm wandering if Joe would look like Max Von Sydow if he had hair & no goatee :unsure: ) 

  • Jeff Moden - Wednesday, November 15, 2017 3:19 PM

    Heh... that's the fun part about standards... there are so many of them. :sick:  

    of course there is a reason there are so many standards...
    https://xkcd.com/927/

Viewing 15 posts - 16 through 30 (of 36 total)

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