CODEWITHSUNDEEP
sqlsql-serverdaterangedate-difference
NotonGoogle
NotonGoogle

Reputation: 3

Calculating the number of dates in specific year, between two dates

I'm trying to figure this out, but it's driving me insane! How do I calculate in SQL the number of days that are in a specific year between two dates? For example: we have the range date [12/30/2016-01/05/2017]. If I need to find how many days are in 2017 between this range of dates, it would be 5 days. How is that done in SQL?

Upvotes: 0

Views: 817

Answers (3)

user330315
user330315

Reputation:

You didn't specify your DBMS, but if your using PostgreSQL you can use a date range to calculate this.

Your question is essentially the length of the intersection between a given date range and the range for "2017":

select daterange('2016-12-30', '2017-01-05', '[]') * daterange('2017-01-01', '2018-01-01')

The upper bound is excluded by default in a daterange, that's why the range for "2017" specifies 2018-01-01 as the (excluded) upper bound. The * operator calculates the intersection between those two ranges. I assumed that you want to include 2017-01-05 in the rage to be tested, that's why it's created with an inclusive upper bound ('[]').

The length is then calculated by subtracting the lower bound from the upper bound:

select upper(result) - lower(result) as days
from ( 
   select daterange('2016-12-30', '2017-01-05', '[]') * daterange('2017-01-01', '2018-01-01')
) t(result)

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

The practical answer to this is "it depends on the database vendor" as even simple date calculations vary substantially e.g.

MySQL

SELECT DATEDIFF('2017-01-05','2016-12-30');

TSQL (MS SQL Server)

SELECT DATEDIFF ( day,'2016-12-30', '2017-01-05')

Postgres

SELECT '2017-01-05'::DATE - '2016-12-30'::DATE

Firebird and in Postgres there are alternatives to that also.

Oracle

SELECT (date '2017-01-05') - (date '2016-12-30') from dual

and in Oracle there are alternatives to that also.

Firebird

select datediff(day,cast('2016-12-30' as date),cast('2017-01-05' as date)) from rdb$database

Your request involves a need to manipulate dates so it is necessary to know which database.

Upvotes: 0

SOS
SOS

Reputation: 6550

You didn't specify your dbms, but in general terms:

  • Get the 1st day of the @EndDate year i.e. January 1, 2017
  • Then calculate the days difference between @FirstOfYear and @EndDate

For example in MySQL, you could use MAKEDATE() to get the first of the year. Then DATEDIFF() to calculate the number of days

SET @StartDate = '2016-12-30';
SET @EndDate = '2017-01-05';  

SELECT DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1 AS DaysInEndDateYear

Result:

| DaysDiffValue|
| ----------------: |
|                 5 |

If you also need to handle ranges where both @StartDate and @EndDate are in the same year:

SET @StartDate = '2017-01-05';
SET @EndDate = '2017-03-14';  

SELECT CASE WHEN Year(@StartDate) = Year(@EndDate) THEN DateDiff(@EndDate, @StartDate) + 1
            ELSE DateDiff(@endDate, MAKEDATE(Year(@endDate),1)) + 1
       END AS DaysInEndDateYear

Results:

| DaysInEndDateYear |
| ----------------: |
|                69 |

db<>fiddle here

Upvotes: 0

Related Questions