SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 finding out how many hours are between two dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meltingchain
Starting Member

3 Posts

Posted - 06/17/2013 :  12:22:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 06/17/2013 :  12:37:03  Show Profile  Reply with Quote
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

Edited by - James K on 06/17/2013 12:37:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000