November 14, 2017 at 12:04 am
Comments posted to this topic are about the item Interesting Datenames
November 14, 2017 at 12:14 am
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.
November 14, 2017 at 12:20 am
Good question, thanks Steve.
...
November 14, 2017 at 1:22 am
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”
November 14, 2017 at 1:42 am
The correct answer is not 02, it is 2...
November 14, 2017 at 1:48 am
Oops.... missed that the default was US_English (dozy berk)!
November 14, 2017 at 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.
November 14, 2017 at 2:49 am
carl.eaves - Tuesday, November 14, 2017 2:44 AMGot 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”
November 14, 2017 at 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.
November 14, 2017 at 6:38 am
Stewart "Arturius" Campbell - Tuesday, November 14, 2017 2:49 AMcarl.eaves - Tuesday, November 14, 2017 2:44 AMGot 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.
November 14, 2017 at 8:35 am
Ed Wagner - Tuesday, November 14, 2017 6:38 AMStewart "Arturius" Campbell - Tuesday, November 14, 2017 2:49 AMcarl.eaves - Tuesday, November 14, 2017 2:44 AMGot 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/
November 14, 2017 at 9:18 am
As with AS on a previous problem, the best advice to answering this question is "MUST LOOK EYE!" 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 14, 2017 at 10:21 am
Mighty - Tuesday, November 14, 2017 3:16 AMSomething 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')
November 14, 2017 at 10:23 am
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
November 14, 2017 at 10:23 am
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 35 total)
You must be logged in to reply to this topic. Login to reply