SQLTeam.com | Weblogs | Forums

Date Time Question


Hi All,

I am stuck with Bad data or something. I have to deal with this type of data no excuse for me. Please don't ask me why :(.

Here is Sample data I have.

6/29/2015 6:28 AM
6/29/2015 6:58
6/29/2015 24:15:00
6/29/2015 25:30:00

My Question is How should I convert this field. My destination field is in SQL and data type is DATETIME.

Please advise.

Thank You.


What format is the original data in? String? What error are you getting when you import the data?
I would think that you could simply CAST this string data, a la:CAST(Order_Date_Time as datetime). If the order of the month and day is an issue, there is a SET DATEFORMAT command that you can run before the import.


Try this (it will not handle microseconds):

create table #yourtable(Order_Date_Time varchar(30),realdatetime datetime);
insert into #yourtable
 values ('6/29/2015 6:28 AM',null)
       ,('6/29/2015 6:58',null)
       ,('6/29/2015 24:15:00',null)
       ,('6/29/2015 25:30:00',null)

update a
   set realdatetime=dateadd(ss
  from #yourtable as a
       inner join (select distinct
                         ,convert(datetime,substring(Order_Date_Time,1,charindex(' ',Order_Date_Time)-1),101) as d
                         ,convert(int,substring(Order_Date_Time,charindex(' ',Order_Date_Time)+1,charindex(':',Order_Date_Time)-(charindex(' ',Order_Date_Time)+1)))
                             when lower(right(rtrim(substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,len(Order_Date_Time))),2))='pm'
                             then 12
                             else 0
                          end as h
                         ,convert(int,substring(Order_Date_Time,charindex(':',Order_Date_Time)+1,2)) as m
                         ,case lower(substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,2))
                             when 'am' then 0
                             when 'pm' then 0
                             when '  ' then 0
                             else convert(int,substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,2))
                          end as s
                     from #yourtable
                  ) as b
               on b.Order_Date_Time=a.Order_Date_Time

select * from #yourtable;

drop table #yourtable;


Hi Bitsmed,

Thank You for your help. It works fine with sample data. Let me implement on actual data and I will update you. Thank You for your time...