DATEDIFF - Adding the half hour!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATEDIFF - Adding the half hour!

Author

Topic

sampcuk
Starting Member

32 Posts

Posted - 2008-08-04 : 06:18:57

This may be a very sill question... I have an SQL that returns the amount of time missed for a course i.e. 9am-10am then it returns '1'. However if the course runs 9am-10.30am it still returns '1' and not '1.5'...

Can anyone help with this? Have been onto books online and am still stumped!

SELECT (RTRIM(Employee.FORENAME)+" "+eMPLOYEE.SURNAME) AS Custom,
Employee.DEPARTMENT AS Department,
Trainrec.COURSE_REF AS Course_ref,
Trainrec.COURSENAME AS Coursename,
Trainrec.STATUS AS Status,
Trainrec.STARTTIME AS Starttime,
Trainrec.FINISHTIME AS Finishtime,
((SELECT DATEDIFF(HOUR,TRAINREC.STARTTIME,TRAINREC.FINISHTIME))) AS TimeMissed
FROM dbo.EMPLOYEE AS Employee
INNER JOIN dbo.TRAINREC AS Trainrec
ON Employee.EMPLOY_REF = Trainrec.EMPLOY_REF
WHERE (((Trainrec.STATUS LIKE '%CANCELLED%' OR Trainrec.STATUS LIKE '%ABSENT%')
AND (Trainrec.STARTTIME >= '28/04/2008')))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 06:21:13

find the datediff in minute and divide by 60.0

KH
[spoiler]Time is always against us[/spoiler]

sampcuk
Starting Member

32 Posts

Posted - 2008-08-04 : 06:22:30

Hi,
Yes I did this which does return the required result but only in minutes. Is there no way to return the value in hours with the half hour included?
Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 06:25:41

if you divide it by 60.0 (as suggested) the result will not be in minutes... 90 / 60.0 = 1.5

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 06:26:24

select datediff(minute,'2008-08-04 9:00:00','2008-08-04 10:30:00')/60.0

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 06:26:29

quote:
Originally posted by sampcuk

Hi,
Yes I did this which does return the required result but only in minutes. Is there no way to return the value in hours with the half hour included?
Thanks

divide by 60.0 will give you in hour

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 06:26:54