Post reply

Date Conversions

  • June 13, 2012 at 10:25 am

    #1500515

    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

  • June 20, 2012 at 9:35 am

    #1503549

    Great question on the Date Conversions.

    Thanks.

  • July 9, 2012 at 7:14 pm

    #1510654

    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

  • August 9, 2012 at 8:47 am

    #1523839

    Interesting question, thanks!

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

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