How to Calculate No of Days

Question

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

Naimish Makwana · 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' Thanks Naimish Makwana

Uday Dodiya · Answer

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 Output :

Jayraj Chhaya · Answer

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 Nath · Answer

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) ) -- 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)