Extracting year and total days between range of date - SQL

Question

I have data with start date and end date (Say 20th Feb 2018 to 20th Feb 2020), I want to find out the total days in every year inside this range.

For example:

2018 - x days
, 2019 - 365 days
, 2020 - y days etc.

Is there a way I can do in SQL without hardcoding year values?

I tried hardcoding the values and it worked well. But I want a solution without hardcoding year values

Does this answer your question? [how to calculate number of days in year in sql server 2008](https://stackoverflow.com/questions/31780606/how-to-calculate-number-of-days-in-year-in-sql-server-2008) — shanmukha vangaru, Feb 11 '23 at 18:54
No, that sql server 2008 question contains sql server syntax that won't work in the context of OP's mysql server. — J_H, Feb 11 '23 at 18:55
This solution has SQL Server solution but it also adds hardcoded values. Is there a way I can get around this problem? — user21194436, Feb 11 '23 at 18:57
This sounds like a place for a recursive CTE to get the years or each day between those dates and then count and group by year. — ClearlyClueless, Feb 11 '23 at 21:33

score 0 · Answer 1 · answered Feb 11 '23 at 21:55

I'm not familiar enough with MySql to know if this will port, however here is a tested and confirmed SQL Server solution.

The fiddle link is here for your use.

Given start dates 02/20/2018 and 02/20/2020, the result set is as follows:

Year	periodStart	periodEnd	DaysInPeriod
2018	2018-02-20	2018-12-31	314
2019	2019-01-01	2019-12-31	365
2020	2020-01-01	2020-02-20	51

    Declare @StartDate date = '2018-02-20', @EndDate date = '2020-02-20';
  
    WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)),
    Years AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Year
        FROM x ones, x tens, x hundreds, x thousands)
  
    SELECT Years.Year,
        CASE 
            WHEN Year(@StartDate) = Years.year THEN @StartDate 
            ELSE DATEFROMPARTS(years.year, 01, 01) 
        END AS periodStart,
        CASE 
            WHEN Year(@EndDate) = Years.year THEN @EndDate 
            ELSE DATEFROMPARTS(years.year, 12, 31) 
        END AS periodEnd,
        DATEDIFF(day, 
            CASE 
                WHEN Year(@StartDate) = Years.year THEN @StartDate 
                ELSE DATEFROMPARTS(years.year, 01, 01) 
            END, 
            CASE 
                WHEN Year(@EndDate) = Years.year THEN @EndDate 
                ELSE DATEFROMPARTS(years.year, 12, 31) 
            END
        ) + 1 AS DaysInPeriod
    FROM Years 
    WHERE Years.Year >= Year(@StartDate) 
        AND Years.Year <= Year(@EndDate)

SelVazi · Answer 2 · 2023-02-11T23:41:32.280

Using WITH RECURSIVE to create range of dates then we can easly count the number of days for each year using DATEDIFF

WITH RECURSIVE dates AS
(
  SELECT min(start_date) as start_date, DATE_FORMAT(min(start_date),'%Y-12-31') as last_day FROM mytable
  UNION ALL
  SELECT DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01'),
  DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-12-31')
  FROM dates
  WHERE DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01')  <= (SELECT MAX(end_date) FROM mytable)
),
cte2 as (
SELECT d.start_date as start_day, if(YEAR(d.start_date) = YEAR(m.end_date), m.end_date, d.last_day) as last_day
FROM dates d, mytable m
)
select *, DATEDIFF(last_day, start_day)+1 as total_days
from cte2;

Demo here

You'll need a +1 on your DateDiff function. DateDiff does not seem to be completely inclusive and is only counting 364 days for 1/1/2019 to 12/31/2019. Noticed that on my own solution as well. — ClearlyClueless, Feb 11 '23 at 22:47

J_H · Answer 3 · 2023-02-11T18:57:25.627

-1

You are looking for the DATEDIFF function.

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions.

You are free to specify e.g. "2019-01-01" or "2020-01-01" as input arguments to DATEDIFF.

You may find it convenient to store several January 1st dates in a calendar reporting table, if you want SELECT to loop over several years and report on number of days in each year.

edited Feb 11 '23 at 18:57

answered Feb 11 '23 at 18:53

J_H

17,926
4
24
44

But it does not tell me the total days grouped by every year right? – user21194436 Feb 11 '23 at 18:54

Extracting year and total days between range of date - SQL

3 Answers3