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

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?

CodePudding user response：

The practical answer to this is "it depends on the database vendor" 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

CodePudding user response：

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