Question 20856 in Mysql Question posted in
The official MySQL documentation can be found here.

Extracting year and total days between range of date – SQL – Mysql

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

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search