Get DAYs of data between two dates from a date range

Question

I have a table Named Bills and it holds data like this

BillID     | From_date  | To_date
-----------+------------+------------
3          | 2017-01-01 | 2017-01-30
6          | 2017-02-06 | 2017-02-25
8          | 2017-02-05 | 2017-02-28

I want to Get DAYs of data between two dates from a date range

For example, if I am searching between 2017-01-25 and 2017-02-02

result is:

6 Day. because between (2017-01-25 and 2017-02-02) and my table 9 days is shared

example 2, if I am searching between 2017-01-01 and 2017-01-05

result is:

5 Day. because between 2017-01-01 and 2017-01-05 and my table 5 days is shared

Kindly check this duplicate link. https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server — JIKEN, Dec 12 '19 at 07:47
Why is your first example `9` days? Between `2017-01-25` and `2017-02-02` you only have 6 days with an overlapping `Bills` row. — iamdave, Dec 12 '19 at 14:27

score 1 · Answer 1 · answered Dec 12 '19 at 07:50

1

you can use datediff function which is used display the difference of start date and end date

select *,DATEDIFF(day,From_date,To_date) days_diff from #temp

answered Dec 12 '19 at 07:50

gady RajinikanthB

19
7

This does not answer the question that has been asked. – iamdave Dec 12 '19 at 10:01
then what you want – gady RajinikanthB Dec 12 '19 at 11:50
They want the number of days within a given range that have a `Bill` record 'active'. Details and examples of this are in the question. – iamdave Dec 12 '19 at 14:17

score 1 · Accepted Answer · answered Dec 13 '19 at 08:54

try it:

DECLARE @DateSearch1 DATETIME='2017-03-13'
DECLARE @DateSearch2 DATETIME='2017-09-25'
SELECT *,DATEDIFF(DAY,r.dat1,r.dat2) daysOfRange
FROM
(
SELECT  BillID ,
        CASE WHEN FromDate<@DateSearch1 THEN @DateSearch1 ELSE FromDate END AS dat1,
        CASE WHEN ToDate>@DateSearch2 THEN @DateSearch2 ELSE ToDate END AS dat2
FROM    Bills

WHERE   ( FromDate >= @DateSearch1
          AND FromDate <= @DateSearch2
        )
        AND ( ToDate >= @DateSearch1
              AND ToDate <= @DateSearch2
            ))r

Get DAYs of data between two dates from a date range

2 Answers2