How to Calculate No of Days

Ramco Ramco

2y948SQL

I have Date & no of Yearsas a parameter. I want no of days month wise till Date + No Of Years

Thamks

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana2y ago

Accepted answer

Hello Ramco,

Try below query

WITH RECURSIVE MonthDays AS (
  SELECT
    DATE_TRUNC('month', current_date) AS month_start,
    DATE_TRUNC('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day' AS month_end,
    1 AS month_number
  UNION ALL
  SELECT
    month_start + INTERVAL '1 month',
    month_end + INTERVAL '1 month',
    month_number + 1
  FROM MonthDays
  WHERE month_start + INTERVAL '1 month' <= current_date + INTERVAL '1 year'
)
SELECT
  month_number,
  EXTRACT(MONTH FROM month_start) AS month,
  EXTRACT(YEAR FROM month_start) AS year,
  DATE_PART('day', GREATEST(month_start, current_date) - LEAST(month_end, current_date + INTERVAL '1 year') + INTERVAL '1 day') AS days_in_month
FROM MonthDays;

Thanks

Naimish Makwana

Jayraj Chhaya2y ago

To calculate the number of days month-wise, we can use SQL queries to generate a list of months and their corresponding number of days. Here's an example of how you can achieve this:

-- Assuming you have a table called 'calendar' with a column 'date' of type DATE

-- Step 1: Create a temporary table to store the month-wise results
CREATE TEMPORARY TABLE temp_monthly_days (
    month_name VARCHAR(20),
    days_in_month INT
);

-- Step 2: Generate the month-wise results
INSERT INTO temp_monthly_days
SELECT 
    DATE_FORMAT(date, '%M') AS month_name,
    DAY(LAST_DAY(date)) AS days_in_month
FROM
    calendar
WHERE
    date BETWEEN '2022-01-01' AND DATE_ADD('2022-01-01', INTERVAL {no_of_years} YEAR);

-- Step 3: Retrieve the month-wise results
SELECT
    month_name,
    days_in_month
FROM
    temp_monthly_days;

In the above example, we assume you have a table called 'calendar' with a column 'date' of type DATE. Adjust the table and column names according to your database schema.

To use this query, replace {no_of_years} with the desired number of years. The query will generate a list of month names and their corresponding number of days for the specified duration.

Anandu G Nath2y ago
Check this

DECLARE @StartDate DATE = '2023-01-01'; -- Replace with your start date
DECLARE @NumberOfYears INT = 3; -- Replace with the number of years you want to add

-- Create a CTE to generate months
;WITH MonthCTE AS (
SELECT @StartDate AS StartOfMonth, DAY(EOMONTH(@StartDate)) AS DaysInMonth
UNION ALL
SELECT DATEADD(MONTH, 1, StartOfMonth), DAY(EOMONTH(DATEADD(MONTH, 1, StartOfMonth)))
FROM MonthCTE
WHERE DATEADD(MONTH, 1, StartOfMonth) <= DATEADD(YEAR, @NumberOfYears, @StartDate)
)
-- Select data from the CTE
SELECT FORMAT(StartOfMonth, 'MMM yyyy') AS MonthYear, DaysInMonth
FROM MonthCTE
OPTION (MAXRECURSION 0); -- Specify the recursion level (0 means unlimited)
+1

Uday Dodiya2y ago

Hi Ramco,

Try Below Query

DECLARE @StartDate DATE = '2023-01-01'; -- Your starting date
DECLARE @NumberOfYears INT = 2; -- Number of years to add

-- Create a temporary table to store the results
CREATE TABLE #MonthDayCounts (MonthYear VARCHAR(20), DayCount INT);

-- Calculate the end date
DECLARE @EndDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- Loop through each month and calculate the days
WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #MonthDayCounts (MonthYear, DayCount)
    VALUES (FORMAT(@StartDate, 'MMMM yyyy'), DATEDIFF(DAY, @StartDate, DATEADD(MONTH, 1, @StartDate)));

    SET @StartDate = DATEADD(MONTH, 1, @StartDate);
END

-- Select and display the month-wise day counts
SELECT MonthYear, DayCount
FROM #MonthDayCounts;

-- Clean up the temporary table
DROP TABLE #MonthDayCounts;

Output :

Output