Interesting Datenames

Question

Interesting Datenames

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

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

HappyGeek SSCoach Points: 18726 More actions · Answer 1

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 SSC-Forever Points: 42192 More actions · Answer 2

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 SSC Guru Points: 76944 More actions · Answer 3

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

Toreador SSChampion Points: 11358 More actions · Answer 4

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 SSCrazy Eights Points: 9063 More actions · Answer 5

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.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 6

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Mighty SSCrazy Eights Points: 9063 More actions · Answer 8

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?! 😀

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

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)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 10

Jeff Moden - Wednesday, November 15, 2017 11:50 AM
... I jumped Celko's bones ...

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 12

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)

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 13

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)

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 14

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 15

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/