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)
 datediff
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

833 Posts

Posted - 12/18/2012 :  06:48:27  Show Profile  Reply with Quote
i want to know the hours between two dates?
the work hours is between 08:00-18:00

i have two columns


StartDate              closeDate            Hours   
'2012-12-18 08:00:00','2012-12-18 10:00:00'  2
'2012-12-18 07:00:00','2012-12-18 10:00:00'  2
'2012-12-18 07:00:00','2012-12-18 20:00:00'  10
'2012-12-18 08:00:00','2012-12-19 10:00:00'  12
'2012-12-18 07:00:00','2012-12-20 10:00:00'  22

Edited by - inbs on 12/18/2012 06:49:27

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 12/18/2012 :  08:01:28  Show Profile  Reply with Quote
Simple way is:

DECLARE @timeDiff TABLE(StartDate  datetime, closeDate DATETIME)
insert into @timeDiff
SELECT '2012-12-18 08:00:00','2012-12-18 10:00:00'   union all
SELECT '2012-12-18 07:00:00','2012-12-18 10:00:00'   union all
SELECT '2012-12-18 07:00:00','2012-12-18 20:00:00'  union all
SELECT '2012-12-18 08:00:00','2012-12-19 10:00:00' union all
SELECT '2012-12-18 07:00:00','2012-12-20 10:00:00' 

;with cte AS
(
	SELECT CASE WHEN DATEPART(HH, startDate) < 8 THEN 8 ELSE DATEPART(HH, startDate) END startTime,
	CASE WHEN DATEPART(HH, closeDate) >= 18 THEN 18 ELSE DATEPART(HH, closeDate) END EndTime,
	DATEDIFF(dd, startDATE, CloseDate) noOfDays,
	StartDate,
	closeDate
	FROM @timeDiff
)SELECT (endtime *(noOfDays+1) - startTime) , StartDate, closeDate FROM cte


For more accuracy check this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176582

--
Chandu

Edited by - bandi on 12/18/2012 08:07:45
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