Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Date Conversions Expand / Collapse
Author
Message
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: Yesterday @ 7:29 PM
Points: 8,562, Visits: 9,063
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.


Tom
Post #1315298
Posted Wednesday, June 20, 2012 9:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:32 AM
Points: 483, Visits: 243
Great question on the Date Conversions.

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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>

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


Jamie
Post #1327225
Posted Thursday, August 9, 2012 8:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Interesting question, thanks!
Post #1342708
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse