Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi guys,I need a function that will return No. of Days and No. of Nights in a given period of Date and Time.e.g. 28/11/2012 01:00 and 30/11/2012 12:00Consider belowFrom 00:00 to 06:00 should be considered as DAY and From 06:01 to 23:59 should be considered as NIGHT...Plz reply...
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-11-28 : 07:35:52
Something like this:
SELECT DATEDIFF(dd,@startDate, @endDate) + CASE WHEN CAST(@startDate AS TIME ) > '06:00' THEN -1 ELSE 0 END AS [Days], DATEDIFF(dd,@startDate, @endDate) + CASE WHEN CAST(@endDate AS TIME ) <= '06:00' THEN -1 ELSE 0 END AS [Nights]