Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase «««1234

Date Conversions Expand / Collapse
Posted Wednesday, June 13, 2012 10:25 AM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 9,825, Visits: 11,896
Very good question.

I don't know why people are fussing so much about the answer format; it did put me of at first, made me leave the question for when I might have time to wade through it, but when I came back to the question and worked out what the output should be (nice and easy, not a lot of thionking time) I saw that there would only be one occurrence of 04-11 instead of 11-04 and that would be in the first column of the second select's output. Even with all that rather small-print output that was easy to look for - just took a few seconds - so the answer format didn't make it hard at all.

Something people should beware of (that isn't obvious from this question) is that when a yyyy-aa-bb date string is converted to datetime using cast or using convert with null style parameter dateformat determines whether it is treated as ODBC canonical date format (dateformat dmy) or as ANSI SQL date format (dateformat mdy); while if a style parameter is included with convert then either an error is generated regardless of dateformat or it is converted as if it were ODBC canonical regardless of dateformat. But if cast or conversion with null style parameter is done to datetime2 this format is always interpreted as ODBC canonical, and there is no way or getting from a dateime2 value to a string that uses ANSI SQL format using a single case or conversion - ANSI SQL is not one of convert's supported styles, although in with dateformat mdy and datatype datetime or smalldatetime omitting the style gives you that format.

Post #1315298
Posted Wednesday, June 20, 2012 9:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 17, 2015 3:03 PM
Points: 504, Visits: 259
Great question on the Date Conversions.

Post #1318829
Posted Monday, July 9, 2012 7:14 PM



Group: General Forum Members
Last Login: Wednesday, May 4, 2016 6:32 AM
Points: 2,938, Visits: 830
I read it through, figured out the right answer, then parsed visually against what was there and based on what the right answer was, I "thought" I selected the right answer. Turned out it was the wrong one.

What I saw was that the mdy works for all the dates but only works for the first date (cast as datetime) on the dmy. That made it all worth the struggle.
<confused - and my eyes hurt>

To Steve - you have absolutely the coolest way to read, preview, edit and complete replies on any site I've seen. Can't thank you enough for this great site even though it "occasionally" hurts a bit! I also like that I can whine and get points for it! Kudos.

Post #1327225
Posted Thursday, August 9, 2012 8:47 AM


Group: General Forum Members
Last Login: Tuesday, June 16, 2015 2:34 AM
Points: 424, Visits: 60
Interesting question, thanks!
Post #1342708
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse