question

prasantapaul avatar image
prasantapaul asked

Get number of days within a date range

I have few date ranges in my database table like below. 01-Jan-2014 to 30-Mar-2014 31-Mar-2014 to 31-Mar-2014 Now I have given a date range (say 26-Feb-2014 to 04-Apr-2014) to retrieve the number of days which exists in my database date range. Can you please help me out in this regard?
date
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
Here is probably what you want: There is a table, which holds different ranges. The final select then counts or returns dates in range specified by the where condition, but only those, which are covered by ranges defined in the @range table. DECLARE @ranges TABLE ( RangeID int IDENTITY(1,1) PRIMARY KEY CLUSTERED, DateFrom date, DateTo date ) INSERT INTO @ranges(DateFrom, DateTO) VALUES ('20140101', '20140330'), ('20140320', '20140331'); WITH Tally AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS N FROM sys.all_objects o1, sys.all_objects o2 ), DatesInRange AS ( SELECT R.RangeID, R.DateFrom, R.DateTo, DATEADD(DAY, T.N, R.DateFrom) AS RangeDate FROM @Ranges R CROSS APPLY (SELECT TOP(DATEDIFF(DAY, R.DateFrom, R.DateTo) + 1) N FROM Tally ORDER BY N) AS T ) SELECT COUNT(DISTINCT RangeDate) --COUNT DISTINCT is important if there will be overlaping ranges in the ranges table, so dates are not counted multiple times --DISTINCT RangeDate --To list dates in range, uncomment this line and comment the above line with COUNT FROM DatesInRange WHERE RangeDate BETWEEN '20140226' AND '20150404'
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 I was assuming it was the simple approach.
0 Likes 0 ·
Thanks Pavel
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
Using TSQL you will need to use the [DATEDIFF][1] function (if I understand the question correctly). SELECT DATEDIFF(DD, FromDate, ToDate) [1]: http://technet.microsoft.com/en-us/library/ms189794.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.