Ho to Convert Varchar datatype into datetime

declare @.a varchar(10)
select @.a= shiftstarttime from o_parameter
print @.a

declare @.b varchar(10)
select @.b= shiftendtime from o_parameter
print @.b

declare @.dt varchar(20)
set @.dt=Left(getdate(),12)

print @.dt

declare @.dt1 varchar(20)
set @.dt1=Left(dateadd(dd, 1,getdate()),12)
print @.dt1

declare @.dt3 varchar(20)

set @.dt3= @.dt1 + space(0) + @.a

print @.dt3

declare @.dt4 varchar(20)

set @.dt4= @.dt + space(0) + @.b

print @.dt4

output of above script is as fallow

09.30am
06.30pm
Aug 22 2007
Aug 23 2007
Aug 23 2007 09.30am
Aug 22 2007 06.30pm

now i want to convert @.dt3 and @.dt4 into datetime .

because i wnat to calculate datedifference in hours by using this function

SET @.in_hour=DATEDIFF (HH ,@.D1,@.D2)

where @.D1 and @.D2 are datetime parameters.

but how can i get @.dt3 and @.dt4 into datetime so i can pass it to DATEDIFF() function.

so plz Guide me. or if u know how to write it then plz write here

i already use cast for it but it doesn't work.

so plz reply urgently.

Try this:

Code Snippet

DECLARE @.dt3 varchar(20), @.dt4 varchar(20), @.in_hour int

SET @.dt3 = 'AUG 23 2007 09:30am'

SET @.dt4 = 'AUG 23 2007 06:30am'

SET @.in_hour=DATEDIFF(HH,CAST(@.dt3 as datetime), CAST(@.dt4 as datetime))

SELECT @.in_hour

or

Code Snippet

DECLARE @.dt3 datetime, @.dt4 datetime, @.in_hour int

SET @.dt3 = CAST('AUG 23 2007 09:30am' as datetime)

SET @.dt4 = CAST('AUG 23 2007 06:30am' as datetime)

SET @.in_hour=DATEDIFF(HH,@.dt3,@.dt4)

SELECT @.in_hour

|||

You can use the following query,

Code Snippet

select

Datediff(HH,

cast(Convert(varchar, dateadd(dd, 1,getdate()),101) + ' ' + replace(shiftstarttime,'.',':') as datetime),

cast(Convert(varchar, dateadd(dd, 1,getdate()),101) + ' ' + replace(shiftendtime,'.',':') as datetime))

from

o_parameter

--or

Select datediff(HH,Convert(datetime, replace(shiftstarttime,'.',':')), Convert(datetime, replace(shiftendtime,'.',':')))

from

o_parameter