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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 finding out how many hours are between two dates

Author  Topic 

meltingchain
Starting Member

3 Posts

Posted - 2013-06-17 : 12:22:48
the subject sounds easier than it is.

I have two dates times lets say 2013-03-21 8:00:00 and 2013-03-23 17:00:00. i need the hours spent for the AM and the PM starting each day at 8 and ending at 17.

so the output should look like
Date AM PM
2013-03-21 4 5
2013-03-22 4 5
2013-03-23 4 5

The main problem I'm having is that the sets of dates are going to be different, sometimes it will only be one day other times it will be multiples.

i currently use

DateDiff(hh,cast(Convert(varchar(2),start,108)as int),cast(Convert(varchar(2),12,108)as int)) /24

to find the time in the AM and vise versa to find the time In the PM. but this wouldn't work for multiple days

Any help on where to look would be great.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-17 : 12:37:03
One way to do this is to create a calendar table that has dates, start times and end times and use that as shown below. There are ways that would require fewer lines of code (which I am sure one will post), but this affords you the luxury of:
a) taking care of holidays or vacation days
b) changing your start time and end time at will even on a day by day basis
c) Taking into account fractional hours (like if someone started at 8:17 AM
CREATE TABLE #Calendar(Dt DATE NOT NULL PRIMARY KEY, startTime TIME, endTime TIME)
;WITH cte(Dt) AS
(
SELECT CAST('20130321' AS DATE) UNION ALL
SELECT DATEADD(dd,1,Dt) FROM cte WHERE Dt < '20130331'
)
INSERT INTO #Calendar SELECT Dt, '08:00', '17:00' FROM cte OPTION (MAXRECURSION 0);

DECLARE @startDate DATE = '20130321';
DECLARE @endDate DATE = '20130331';

SELECT
c.Dt,
CASE
WHEN startTime < '12:00' THEN DATEDIFF(mi,startTime,CAST('12:00' AS TIME))/60.0
ELSE 0
END AS AM,
CASE
WHEN endTime > '12:00' THEN DATEDIFF(mi,CAST('12:00' AS TIME),endTime)/60.0
ELSE 0
END AS PM
FROM
#Calendar c


DROP TABLE #Calendar
Go to Top of Page
   

- Advertisement -