conver varchar time into datetime

[ciarra41]

Hi there;
I'm trying to convert this 11:15:AM into a datetime format.
The problem I see is the second ) I've tried to remove it but still I'm getting an error.

I've tried these:
CONVERT(varchar(8),mytime,108)
RTRIM(SUBSTRING(mytime,1,5)+' '+SUBSTRING(mytime,7,2))

any other suggestions.

 

[ciarra41]

actually the second example worked. it was a date field that was causing the error.

Oops!

 

[Qik3Coder]

Careful with times, because it may not correctly convert PM times.

Normally you should be able to:
Slice off the date part
Convert that to a date
then do a dateAdd for the various parts, and if you have a PM you add an extra 12hr to your time.

Note this sample is expecting your data to be padded to 2 chars in each field.
Ex:

declare @_foDate varchar(25), @_realDate datetime
select @_foDate = '01/12/2011 04:15:PM'

Select @_realDate  = cast(left(@foDate, 10) as datetime)
select @_realDate = dateAdd(hh, cast(substring(@foDate, 12, 2) as int)+ CASE WHEN substring(@_foDate, 18, 2) = 'PM' THEN 12 ELSe 0 END, @_realDate)
select @_realDate = dateAdd(mi, cast(substring(@_foDate, 15, 2) as int), @_realDate)
Select @_realDate

Lodlaiden

You've got questions and source code. We want both!