SQLTeam.com | Weblogs | Forums

Date Time Question


#1

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.

Order_Date_Time
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.


#2

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.


#4

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
                           ,b.s
                           ,dateadd(mi
                                   ,b.m
                                   ,dateadd(hh
                                           ,b.h
                                           ,b.d
                                           )
                                   )
                           )
  from #yourtable as a
       inner join (select distinct
                          Order_Date_Time
                         ,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)))
                         +case
                             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;

#5

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...