Working out time difference in hours and minutes

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

Working out time difference in hours and minutes

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-10 : 06:21:22

Hi all

First of all, I'm using SQL server 2008.

I'm trying to get the difference between 2 times in hours and minutes and I've ground to a halt.

This is the code I'm using


	begin
		datediff(hh,st.timestamp,DATEADD(n,15,et.timestamp)) as Shift_hours
		if datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 = 0
			shift_hours=shift_hours+1
		datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 as shift_minutes
		cast(shift_hours as varchar) + ':' + cast(shift_minutes as varchar) as Shift_Time
	end

It's part of a larger select statement using temporary tables that run fine without this code in.
These are the errors I'm getting:-
Msg 156, Level 15, State 1, Line 108
Incorrect syntax near the keyword 'begin'.
Msg 102, Level 15, State 1, Line 109
Incorrect syntax near 'datediff'.
Msg 102, Level 15, State 1, Line 111
Incorrect syntax near 'shift_hours'.

if it helps, the line above the begin statement is :-
DATEADD(n,15,et.End_Time) as End_Time,

Timestamp is a datetime field.

Anyone any ideas where I'm going wrong?

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:25:26

You are missing the select clause.

PBUH

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-10 : 06:33:34

This section of code is in the middle of a select clause.
The entire section of code is as follows :-


select
	case left(emp.UnitName,CHARINDEX(' ',emp.UnitName))
	when 'Hedge' then 'Hedge End'
	when 'Milton' then 'Milton Keynes'
	when 'bolton' then 'Middlebrook'
	else left(emp.UnitName,CHARINDEX(' ',emp.UnitName)) end  as [Site name],
	case emp.SkillGroup
	when 'National WK HA' THEN 'Health Advisor'
	when 'National MK HA' then 'Health Advisor' 
	when 'DT2 - HA' then 'Health Advisor'
	when 'National WK NA' THEN  'Nurse Advisor'
	when 'National MK NA' then 'Nurse Advisor'
	when 'DT2 - NA' then 'Nurse Advisor'
	when 'National Dental' THEN  'Dental'
	when 'DT2 - DN' then 'Dental'
	when 'Health Information - National' THEN  'HI Advisor' end as Skill_Group,
	st.Date,
	st.Employee_ID,
	emp.FirstName,
	emp.FamilyName,
	st.Start_Time,
	DATEADD(n,15,et.End_Time) as End_Time,
        begin
	datediff(hh,st.timestamp,DATEADD(n,15,et.timestamp)) as Shift_hours
	if datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 = 0
		shift_hours=shift_hours+1
	datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 as shift_minutes
	cast(shift_hours as varchar) + ':' + cast(shift_minutes as varchar) as Shift_Time
        end
from
	#start_table st
	join #end_table et on (st.Row=et.row
							and st.Employee_ID=et.Employee_ID)
	join employees_details emp on st.Employee_ID=emp.EmpId
where
	emp.SkillGroup in ('National WK HA','National MK HA','DT2 - HA','National WK NA','National MK NA','DT2 - NA',
		'National Dental','DT2 - DN','Health Information - National')
order by
	[Site name],
	Skill_Group,
	date,
	Start_Time

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 06:44:57

You cannot have a if statement in select clause.


if datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 = 0
		shift_hours=shift_hours+1
	datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 as shift_minutes
	cast(shift_hours as varchar) + ':' + cast(shift_minutes as varchar) as Shift_Time

Use a case instead.

PBUH

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-10 : 06:59:21

Thanks for that - just testing the case statement now.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-10 : 07:21:23

That seems to work OK, but it's adding an hour if the start/finish times go over midnight.
This a copy of my case statement :-


	case datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60
	when 0 then cast(datediff(hh,st.timestamp,DATEADD(n,15,et.timestamp)) as varchar) + ':00'
	else cast(datediff(hh,st.timestamp,DATEADD(n,15,et.timestamp)) as varchar) + ':' + cast(datediff(mi,st.timestamp,DATEADD(n,15,et.timestamp)) % 60 as varchar)
	end as Shift_hours

Anyone any ideas why it's adding an hour?

::edit::

Just noticed it only seems to add an hour if the start time is half past or quarter to the hour and the finish time is on the hour.

Don't know if this helps or not, but thought it might be useful.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-10 : 09:16:01

Quick update to say - solved it.
I was being an idiot (as usual) and using the case statement badly.
It's all now sorted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources