SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Interesting Datenames


Interesting Datenames

Author
Message
Sean Lange
Sean Lange
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125038 Visits: 18395
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)SSC Guru (424K reputation)

Group: General Forum Members
Points: 424992 Visits: 43411
As with AS on a previous problem, the best advice to answering this question is "MUST LOOK EYE!" Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NBSteve
NBSteve
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2482 Visits: 1005
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')


chgn01
chgn01
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2413 Visits: 2017
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
Jason A. Long
Jason A. Long
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7695 Visits: 5391
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?
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5557 Visits: 3036
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

...
Chris Harshman
Chris Harshman
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19588 Visits: 5501
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.

drew.allen
drew.allen
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30597 Visits: 12571
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Toreador
Toreador
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5329 Visits: 8220
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.

Mighty
Mighty
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5733 Visits: 1837
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search