Interesting Datenames

  • Comments posted to this topic are about the item Interesting Datenames

  • Setting the date is depending on the language/region setting of SQL server.
    he server I tested on interpreted the string '10/02/17' as February 10th, DATENAME returned 10.
    I picked the wrong answer. 

    Louis.

  • Good question, thanks Steve.

    ...

  • The database i tested my answer on has "us_english" as the default language.
    the script would therefore interpret the date provided as  MM/dd/yyyy

    Interestingly, if the SET LANGUAGE British was placed at the top of the script, the  date would be interpreted as dd/MM/yyyy...

    Language settings are important....

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

  • The correct answer is not 02, it is 2...

  • Oops.... missed that the default was US_English (dozy berk)!

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

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

    I'm one of those that live outside the US. However, my current contract involves working with a company that spans several continents, each with their own date format peculiarities...

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

  • Something that might not be clear is that only the date format is not changed until the end of the batch. What the DATENAME returns is directly affected.

    DECLARE @RunDate DATE = '10/02/17'
    SET LANGUAGE Italian
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)
    SET LANGUAGE US_English
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)

    For me that feels a bit inconsistent.

  • Stewart "Arturius" Campbell - Tuesday, November 14, 2017 2:49 AM

    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.

    I'm one of those that live outside the US. However, my current contract involves working with a company that spans several continents, each with their own date format peculiarities...

    I live and work in the US, but have the privilege of working with data for many different countries.  There are a lot of date formats out there and the same applies to strange number formats.  Customers in different countries don't always follow their own standards.  If you have to work with global data, then it's a normal part of daily life.

  • Ed Wagner - Tuesday, November 14, 2017 6:38 AM

    Stewart "Arturius" Campbell - Tuesday, November 14, 2017 2:49 AM

    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.

    I'm one of those that live outside the US. However, my current contract involves working with a company that spans several continents, each with their own date format peculiarities...

    I live and work in the US, but have the privilege of working with data for many different countries.  There are a lot of date formats out there and the same applies to strange number formats.  Customers in different countries don't always follow their own standards.  If you have to work with global data, then it's a normal part of daily life.

    Indeed. I am in the US and have been living here my entire life. However, I work for a global company and dealing with different date formats is normal.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • As with AS on a previous problem, the best advice to answering this question is "MUST LOOK EYE!" 😉

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

  • Mighty - Tuesday, November 14, 2017 3:16 AM

    Something that might not be clear is that only the date format is not changed until the end of the batch. What the DATENAME returns is directly affected.

    DECLARE @RunDate DATE = '10/02/17'
    SET LANGUAGE Italian
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)
    SET LANGUAGE US_English
    SELECT DATENAME(dd, @RunDate)
    SELECT DATENAME(MONTH, @RunDate)

    For me that feels a bit inconsistent.

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

  • run the script in sql2012 or sql2016, first time it return 2; run 2nd time it return 10.
    but if you run this:

    SET LANGUAGE British
    DECLARE @RunDate DATE = '10/02/17'
    SELECT DATENAME(dd, @RunDate)

    it will only return 10

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • It's a trick question... The answer will change between the 1st and 2nd execution due to the fact that the language setting wont change mid batch.
    So... Since you're starting of w/ US_English, the 1st execution will return "2". Any subsequent executions will return "10".
    Steve - Which answer are you looking for?

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

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