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?

## Advertisement

## Answer

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*

**10**People found this is helpful