How to find the total no. of hrs b/w time interval

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)

How to find the total no. of hrs b/w time interval

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-02-23 : 09:57:42

Hi,

I want the query to find the total number of hours between the time night 20:00 to next day morning 6:00

consider the below example:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '01/01/2011 17:00'
SET @endDate = '01/02/2011 01:30'

select datediff(ss,@startDate,@endDate)/3600.0

In the above example we have total hours difference is 8.5, but i want to find out the total nightHours in above case it is 5.5 hours
(The hours difference between 20:00 to next day morning 6:00)

please advise me.....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-23 : 12:20:46

Well here's one (EXCEEDINGLY HORRIBLE) way to do it.


DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '01/01/2011 17:00'
SET @endDate = '03/02/2011 01:30'

select datediff(ss,@startDate,@endDate)/3600.0 


--StartDateEveningStarts
DECLARE @startDateEv DATETIME
	SET @startDateEv = DATEADD(DAY, DATEDIFF(DAY, '19000101', @startDate), '19000101')
	SET @startDateEv = DATEADD(HOUR, 20, @startDateEv)


DECLARE @endDateMorning DATETIME
	SET @endDateMorning = DATEADD(HOUR, 12, @startDateEv)

DECLARE @adjustedEndDate DATETIME
	SET @adjustedEndDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @endDateMorning), '19000101')
						   + DATEADD(DAY, -DATEDIFF(DAY, '19000101', @endDate), @endDate)
	
SELECT @startDateEv, @endDateMorning, @adjustedEndDate

SELECT
	((DATEDIFF(DAY, @startDate, @endDate) - 1 )* 12 * 60 * 60 -- Whole Days of darkness!
	+ DATEDIFF(
		SECOND
		, CASE WHEN @startDate > @startDateEv THEN @startDate ELSE @startDateEv END
		, CASE WHEN @adjustedEndDate < @endDateMorning THEN @adjustedEndDate ELSE @endDateMorning END
		)) / 3600

THis is more of a joke than anything else but I think it generates the right answer.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-23 : 12:51:26

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '01/01/2011 17:00'
SET @endDate = '01/02/2011 01:30'

SET @startdate = CASE WHEN datepart(hour,@startdate) < 20
THEN DATEADD(hour,20,dateadd(day,datediff(day,0,@startDate),0))
ELSE @startDate
END

SET @endDate
= CASE WHEN datepart(hour,@enddate) > 5
THEN DATEADD(hour,5,dateadd(day,datediff(day,0,@endDate),0))
ELSE @endDate
END

select @startDate,@endDate

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources