Question

  • Creator
    Topic
  • #2213279

    how to convert/format date format in sql server

    Locked

    by jazzygodfrey ·

    hi im using sql 2005, I have a varchar column with values in the date format as
    21-09-1982 meaning 21-sep-1982
    23-05-1981 ” 23-may-1981
    25-12-1972 ” 25-dec-1972

    I want to format them to read in the format 21-sep-1982 (dd-mmm-yyy)
    but it seems my sql server reads them as mm-dd-yyyy when i try to use convert or cast
    it says the conversion resulted in an out of range value.
    How can i format my sql to read them as dd-mmm-yyyy?

All Answers

  • Author
    Replies
    • #2869613

      Clarifications

      by jazzygodfrey ·

      In reply to how to convert/format date format in sql server

      Clarifications

    • #2869602

      SQL Server Date Formats

      by peconet tietokoneet ·

      In reply to how to convert/format date format in sql server

    • #2869576

      Formating only works on date types

      by tony hopkinson ·

      In reply to how to convert/format date format in sql server

      and it will generally work by locale. Unfortuately as MS is designed in the US, it often as a default or through error drops into us format, so I personally never trust it in this sort of problem.

      Best bet is to rearrange the varchar in a universal format (YYYY-MM-DD) and then convert that to a date and then convert it to the desired format on output.

      This is going to be messy

      Select SubString([DuffDate],7,4) + ‘-‘ + SubString([DuffDate],4,2) + ‘-‘ + SubString([DuffDate],1,2) From DuffDates

      Would give you a string containing universal formatted date, so

      Select Convert(DateTime,SubString([DuffDate],7,4) + ‘-‘ + SubString([DuffDate],4,2) + ‘-‘ + SubString([DuffDate],1,2)) From DuffDates

      Gives you a dateTime and then wrap that in convert for teh required formatting.

      This is the sort of problem you should fix at source if you can and make this naff column a date.

      Another option might be to add a datatime column to it and then do an UPdate … Where ProperDate is Null before processing.

      • #2868621

        Formating only works on date types

        by jazzygodfrey ·

        In reply to Formating only works on date types

        Thanks Tony
        I Actually tried option 2 before i read you post, and it did work for me, although I tried option one before but not in a universal formating date like yours because at first the date was in string like ‘11052010’ so i had to use the substing function to rearrange in date formart like 11-05-2010 and later used the option two of yours.

        But anyways you gave me the best and straight foward solution im gonna use next time.
        Thanx a million Tony.

        • #2868603

          Always use a universal

          by tony hopkinson ·

          In reply to Formating only works on date types

          for passing dates as strings or integers.
          NB if toy pass them as floats, don’t forget different systems can have different epochs.

          Always, no exceptions. If the source or sink need different format they should take care of their own, any other way is just an issue waiting to be discovered.

          I’ve seen some real head scratchers in my time, especially in windows api routines.

          My all time favourite was one with some logic in it to guess between MM/DD and DD/MM. So as soon as you got past the 12th it worked but before that it picked the short date format in regional settings. Given it was a ‘time stamp’ function it all worked when we tested it,

          at the end of the month. 🙁 🙁

Viewing 2 reply threads