December 14, 2008 at 8:56 am
I have imported data from a flat file to SQL Express 2008. Data type of Date_Time column is varchar(50). the datetime format of imported data column is "11-OCT-08 08.18.40.000000 PM". How do i convert this column to datetime data type?
December 14, 2008 at 10:52 am
Going on the assumption that the information is standardized in format and will not change
CREATE function dbo.udfGetRealDate (@vardate varchar(50))
RETURNS datetime
AS
BEGIN
declare @datedata datetime
declare @data varchar(25)
select @data = LEFT(@vardate, 2) + '/' +
CASE
WHEN SUBSTRING(@vardate, 4, 3) = 'JAN' THEN '01'
WHEN SUBSTRING(@vardate, 4, 3) = 'FEB' THEN '02'
WHEN SUBSTRING(@vardate, 4, 3) = 'MAR' THEN '03'
WHEN SUBSTRING(@vardate, 4, 3) = 'APR' THEN '04'
WHEN SUBSTRING(@vardate, 4, 3) = 'MAY' THEN '05'
WHEN SUBSTRING(@vardate, 4, 3) = 'JUN' THEN '06'
WHEN SUBSTRING(@vardate, 4, 3) = 'JUL' THEN '07'
WHEN SUBSTRING(@vardate, 4, 3) = 'AUG' THEN '08'
WHEN SUBSTRING(@vardate, 4, 3) = 'SEP' THEN '09'
WHEN SUBSTRING(@vardate, 4, 3) = 'OCT' THEN '10'
WHEN SUBSTRING(@vardate, 4, 3) = 'NOV' THEN '11'
WHEN SUBSTRING(@vardate, 4, 3) = 'DEC' THEN '12'
END
+ '/' + SUBSTRING(@vardate, 8, 2)
+ ' ' + SUBSTRING(@vardate, 11, 2)
+ ':' + SUBSTRING(@vardate, 14, 2)
+ ':' + SUBSTRING(@vardate, 17, 2)
+ RIGHT(@vardate, 2)
select @datedata = convert(datetime, @data)
RETURN @datedata
END
Call it with select dbo.udfGetRealDate('11-OCT-08 08.18.40.000000 PM')
This also presumes that you don't need the millisecond data (simple enough to just add that)
I am sure that there are far more elegant versions of this that would work with a simple convert, I just couldn't figure any out.
December 14, 2008 at 11:52 am
Nicholas Cain (12/14/2008)
I am sure that there are far more elegant versions of this that would work with a simple convert, I just couldn't figure any out.
I don't know about elegant, but this seems to do the trick
select convert(datetime, LEFT(REPLACE(REPLACE('11-OCT-08 08.18.40.000000 PM','-',' '),'.',':'), 22), 113)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2008 at 12:00 pm
Like I said simple convert 😀
December 15, 2008 at 3:13 am
Thankyou verymuch for the help. The function by Nicholas Cain works fine but the single line code by GilaMonster makes a mistake. it should convert time (with AM/PM suffix) to 24 hour system. Sample output from a few records is as below
11-OCT-08 08.18.40.000000 PM2008-10-11 08:18:40.000
03-OCT-08 02.14.57.000000 AM2008-10-03 02:14:57.000
10-OCT-08 08.47.02.000000 PM2008-10-10 08:47:02.000
As can be seen the conversion of record with AM suffix is ok but with PM it should be 20 instead of 08
in the hours field after conversion.
Any way thanks to both of you. The problem has been solved.
December 15, 2008 at 9:33 am
rahydri (12/15/2008)
Thankyou verymuch for the help. The function by Nicholas Cain works fine but the single line code by GilaMonster makes a mistake. it should convert time (with AM/PM suffix) to 24 hour system. Sample output from a few records is as below11-OCT-08 08.18.40.000000 PM2008-10-11 08:18:40.000
03-OCT-08 02.14.57.000000 AM2008-10-03 02:14:57.000
10-OCT-08 08.47.02.000000 PM2008-10-10 08:47:02.000
As can be seen the conversion of record with AM suffix is ok but with PM it should be 20 instead of 08
in the hours field after conversion.
Any way thanks to both of you. The problem has been solved.
Ok, so just add a case statement to check for AM/PM:
select CASE WHEN substring('11-OCT-08 08.18.40.000000 PM',27,1)='P'
THEN dateadd(hh,12,convert(datetime, LEFT(REPLACE(REPLACE('11-OCT-08 08.18.40.000000 PM','-',' '),'.',':'), 22), 113))
ELSE convert(datetime, LEFT(REPLACE(REPLACE('11-OCT-08 08.18.40.000000 PM','-',' '),'.',':'), 22), 113)
END
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 15, 2008 at 9:27 pm
Thanks. It works fine now
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply