CODEWITHSUNDEEP
sql-serverdate
Morteza Jangjoo
Morteza Jangjoo

Reputation: 1790

Get DAYs of data between two dates from a date range

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

Upvotes: 1

Views: 145

Answers (2)

Joe herman
Joe herman

Reputation: 134

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

Upvotes: 1

gady RajinikanthB
gady RajinikanthB

Reputation: 17

enter image description hereyou 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

Upvotes: 1

Related Questions