April 29, 2009 at 8:30 am
I have a data source that provides dates in a varchar format (e.g. AUG 28, 2008@11:30:03) that I need to convert to a datetime format. Any suggestions?
Thanks in advance.
April 29, 2009 at 8:43 am
How about this...
select Cast(Replace('AUG 28, 2008@11:30:03', '@', ' ') as datetime)
April 29, 2009 at 9:07 am
Just a slight addition to Ken's (correct!) answer:
SELECT CONVERT(DATETIME, REPLACE('AUG 28, 2008@11:30:03', '@', ' '), 109)
CONVERT with style 109 ensures that the correct format is taken.
Greets
Flo
April 30, 2009 at 7:37 am
Just what the doctor ordered. Thanks a bunch.
February 11, 2010 at 11:29 am
CREATE TABLE [dbo].[_CalendarDate](
[F1] [varchar](20) NULL
) ON [PRIMARY]
select * from dbo._CalendarDate
8242004
8252004
8262004
select CONVERT(datetime, F1, 103) from _CalendarDate
Syntax error converting datetime from character string.
I want to convert it to datetime, any idea?
February 11, 2010 at 11:38 am
CooLDBA (2/11/2010)
CREATE TABLE [dbo].[_CalendarDate]([F1] [varchar](20) NULL
) ON [PRIMARY]
select * from dbo._CalendarDate
8242004
8252004
8262004
select CONVERT(datetime, F1, 103) from _CalendarDate
Syntax error converting datetime from character string.
I want to convert it to datetime, any idea?
Hard to say. Looks like the dates are in mmddyyyy format, but potentially with no leading zeros. If there are no leading zeros in the DD portion, it could be problematic. From your sample data, I have no clue.
February 11, 2010 at 2:56 pm
I was able to change the data to
08-24-2004
08-25-2004
08-26-2004
select CONVERT(datetime, F3, 103) from _CalendarDate
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(1 row(s) affected)
any clue?
February 11, 2010 at 3:10 pm
CooLDBA (2/11/2010)
I was able to change the data to08-24-2004
08-25-2004
08-26-2004
select CONVERT(datetime, F3, 103) from _CalendarDate
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(1 row(s) affected)
any clue?
try this:
select CONVERT(datetime, F3, 110) from _CalendarDate
February 11, 2010 at 3:58 pm
Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
It didn't make sense as if I do:
SELECT convert(datetime,'2004-08-24',120)
2004-08-24 00:00:00.000
It works!
February 11, 2010 at 4:13 pm
Sorry guys. I figured it out.
There is erroneous data value in the table. =(
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply