Conversion of dates format??

  •        I've a text file which consist of one column of dates, in that the date format is :

           Web Nov 15 07:20:46 GMT -07:00 2006

           I want to use this file in bulk insert as the date format should be as:

           2006/12/13 16:26:13:00

           How to achieve this one in SQL,

            Any suggestions and answers are always welcome....

     

       in advance thanks....

  • I think you're going to struggle, however cleverly you parse the input.  The input string is missing a year.  If you know for certain that all data is from a particular year you *might* be able to do something like:

    select  convert (datetime,substringcolumnname, 5, 7) + '2006 ' + substringcolumnname,12,8))

    from mytable

    But I wouldn't advise that for any purpose other than doing a one-time-only cleansing of data that you're absolutely certain was for that year.

    Good luck

  • SDM,

    The poster's original string DOES have a year - it's at the very end.

    I want to know how you get "2006/12/13 16:26:13:00" from "Web Nov 15 07:20:46 GMT -07:00 2006" I would have expected it to become 2006/11/15 00:20:46.

    -SQLBill

  • Memo to self.  Read original posts carefully. 

    OK, so the conversion should be

    select convert(datetime,substring(columnname, 5, 7) + substring(columnname,32,4) + substring(columnname,11,9))

    from mytable

     

  • Uhh..  and I was assuming that (a)  the 'Web' part was a typo and that it was supposed to be 'Wed' and (b) that the output was intended to stay as a time-zone-specific form rather than being adjusted by the GMT -07:00:00 part.

    If you do that, shouldn't you subtract the displacement from GMT to get an adjusted GMT/UTC datetime?  Wouldn't the GMT time be 14:20:46?

Viewing 5 posts - 1 through 4 (of 4 total)

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