Creating SQL Function with Date Range Parameters to Return Time Period Data

Data and Databases
1

I’m trying to create a table-valued function in SQL that takes two date parameters and returns a monthly breakdown with quarters, semi-annual, and yearly data.

What I want to achieve:
Call the function like: select * from getDatePeriods('2012-01-01','2015-09-09')

Expected output format:

month_num  quarter  half_year  year_val
    1        1         1        2012
    2        1         1        2012
    3        1         1        2012
    4        2         1        2012
    5        2         1        2012
    6        2         1        2012
    7        3         2        2012
    8        3         2        2012
    9        3         2        2012
   ...      ...       ...       ...
    9        3         2        2015

My current attempt:

create function getDatePeriods
(@beginDate date, @finishDate date)
RETURNS @result table
(month_num int, quarter int, half_year int, year_val int)
AS
BEGIN 
    declare
    @month_num int,
    @quarter int,
    @half_year int,
    @year_val int;
    
    select @month_num = DATEDIFF(MONTH, @beginDate, @finishDate);
    select @quarter = DATEDIFF(QUARTER, @beginDate, @finishDate);
    select @half_year = DATEDIFF(QUARTER, @beginDate, @finishDate) / 2;
    select @year_val = DATEDIFF(YEAR, @beginDate, @finishDate);
    
    WHILE (@finishDate > @beginDate)
    BEGIN
        insert into @result
        select @month_num, @quarter, @half_year, @year_val;
    END;
    
    return;
END;

The function isn’t working as expected. How can I fix this to generate the correct monthly rows with proper quarter and semi-annual calculations for each month in the date range?

2

Your function creates an infinite loop because @beginDate never changes, and you’re calculating static values outside the loop. You need to iterate through each month and calculate the periods dynamically. Here’s a fix:

WHILE (@beginDate <= @finishDate)
BEGIN
    SET @month_num = MONTH(@beginDate);
    SET @quarter = DATEPART(quarter, @beginDate);
    SET @half_year = CASE WHEN MONTH(@beginDate) <= 6 THEN 1 ELSE 2 END;
    SET @year_val = YEAR(@beginDate);
    
    INSERT INTO @result VALUES (@month_num, @quarter, @half_year, @year_val);
    
    SET @beginDate = DATEADD(MONTH, 1, @beginDate);
END;

Key changes: use MONTH() and YEAR() to extract actual values from the current date, calculate half_year based on month number, and increment @beginDate by one month each iteration to avoid the infinite loop.

3

yeah, ur while loop is stuck! you gotta increment @beginDate by month in there. also, quarter and half_year should update with each month, right? that might be why it’s not working!

4

your logic calculates the differences once, but you need to calculate them for each month in the loop. try using MONTH(@beginDate) and DATEPART(quarter, @beginDate) inside the loop instead of datediff. you’re also missing the @beginDate increment that the previous answer mentioned.