convert string to_date /time

[annemie]

Hello,

I have a log in the database witch keeps in one colum the starttime of one activity end in an other the endtime
So : activity|starttime|endtime

I like to subtract these and make the sum off all hours dedicated to one activity

SELECT SUM(DATEDIFF(hour,Starttime,stoptime)) AS Expr1
FROM dbo.Projectlog

But i have got this database where de datatype for Startime and Stoptime is nvarchar(50)
example for a cell in starttime/stoptime: 8:56:01 AM

How do i convert this so i can use it to calaculate the datediff as above ?

Annemie

 

[tlbroadbent]

Use the Cast or Convert function.

Convert(datetime, StartDate)
Cast(StartTime As datetime)

SELECT
SUM(DATEDIFF(hour,
Cast(StartTime As datetime),
Cast(StopTime As datetime))) AS HoursDiff
FROM dbo.Projectlog
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[annemie]

thank you !!!!
This makes my day !