Date Conversions

  • TomThomson

    SSC Guru

    Points: 104773

    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

  • zymos

    SSCommitted

    Points: 1960

    Great question on the Date Conversions.

    Thanks.

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    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

  • Neha05

    Default port

    Points: 1494

    Interesting question, thanks!

Viewing 4 posts - 31 through 34 (of 34 total)

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