December 20, 2006 at 2:40 am
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....
December 21, 2006 at 3:55 pm
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
December 21, 2006 at 4:30 pm
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
December 21, 2006 at 4:35 pm
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
December 21, 2006 at 4:46 pm
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