SQL Function to Increment 6 character ISO date field with format 'YYYYWW'

Question

I have several tables that store weekly data using an 6 character year and ISO week, with format 'YYYYWW'. For example the first week in 2020 would be ‘202001’. I'm using SQL Server 2012(SP1) 11.0.3156.

I am trying to write an SQL function that allows me to increment the week I am on, so that I can write queries that look forward and backwards in time. So my function would work something like this:

PRINT dbo.increment_week ('202244', -2)  would simply return/print ‘202242’

I want the function to take an integer as the 2nd parameter and move that number of weeks, +ive forward in time, -ive backwards in time.

Simple enough, but not all years have 52 weeks. So I wrote the function to cope with that aspect. But other ISO week issues are stopping my function from working correctly (or at least how I would expect it to work). I’m running this in Europe, don’t know if that would impact how the ISO works in SQL, or if that is the issue.

I can’t figure out how to fix this. Has any one got any ideas on how to resolve this? Any help appreciated, thanks. Here is the SQL code and some example outputs:

CREATE FUNCTION dbo.increment_week(@week_to_increment char(6), @increment_weeks int)
RETURNS char(6) 
AS 
BEGIN 

    DECLARE @year_start_date date, @current_week_date date, @new_date date
    DECLARE @Week_Of_Year_Text char(2), @year_Text char(4)
    DECLARE @week_of_first_of_Year int, @current_week_int int
    
    SET @year_start_date        = CAST(LEFT(@week_to_increment, 4) + '0101' AS DATE)
    SET @current_week_int       = CAST(RIGHT(@week_to_increment, 2) AS INT)
    SET @week_of_first_of_Year  = DATEPART(isoww, @year_start_date)
    /* If start week is 53 move on one week */
    SET @year_start_date        = DATEADD(week, IIF(@week_of_first_of_Year = 53, 1, 0), @year_start_date) 
    SET @current_week_date      = DATEADD(week, @current_week_int, @year_start_date) 
    SET @new_date               = DATEADD(week, @increment_weeks, @current_week_date)  
    SET @week_Of_Year_Text      = RIGHT('00'+ CAST(DATEPART(ISO_WEEK, @new_date) AS NVARCHAR(2)), 2)
    SET @year_Text              = CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(day, 26 - DATEPART(isoww, @new_date), @new_date)))
    RETURN @year_Text + @week_Of_Year_Text
END
GO

Here are some sample calls to the function:

PRINT dbo.increment_week ('202244', -2) /* correct returns '202242' */

PRINT dbo.increment_week ('202244', 0) /* correct returns '202244' */

PRINT dbo.increment_week ('202244', 2) /* correct returns '202246' */
 
PRINT dbo.increment_week ('202201', -2) /* correct returns '202251' */

PRINT dbo.increment_week ('202201', 0) /* correct returns '202201' */

PRINT dbo.increment_week ('202201', 2) /* correct returns '202201' */

PRINT dbo.increment_week ('202044', -2) /* wrong returns '202043' not '202042' */

PRINT dbo.increment_week ('202044', 0) /* wrong returns '202045' not '202044' */

PRINT dbo.increment_week ('202044', 2) /* wrong returns '202047' not '202046' */

PRINT dbo.increment_week ('202001', -2) /* wrong returns '201952' not '201951' */

PRINT dbo.increment_week ('202001', 0) /* wrong returns '202002' not '202001' */

PRINT dbo.increment_week ('202001', 2) /* wrong returns '202044' not '202003' */

With some inspiration from the comments I added a case statement which now seems to work. Could be streamlined a bit, but here it is:

CREATE FUNCTION dbo.increment_week(@week_to_increment char(6), @increment_weeks int)
RETURNS char(6) 
AS 
BEGIN 
    DECLARE @year_start_date date, @current_week_date date, @new_date date
    DECLARE @Week_Of_Year_Text char(2), @year_Text char(4)
    DECLARE @week_of_first_of_Year int, @current_week_int int
    
    SET @year_start_date        = CAST(LEFT(@week_to_increment, 4) + '0101' AS DATE)
    SET @week_of_first_of_Year  = DATEPART(isoww, @year_start_date)
    
    /*===========works but code could be simplified?=======*/
    SET @year_start_date = CASE
        WHEN DATEPART(dw, @year_start_date) = 1 
            THEN @year_start_date /* Sunday    */
        WHEN DATEPART(dw, @year_start_date) = 2 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -1, 6), @year_start_date) /* Monday    */
        WHEN DATEPART(dw, @year_start_date) = 3 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -2, 5), @year_start_date) /* Tuesday   */
        WHEN DATEPART(dw, @year_start_date) = 4 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -3, 4), @year_start_date) /* Wednesday */
        WHEN DATEPART(dw, @year_start_date) = 5 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -4, 3), @year_start_date) /* Thursday  */
        WHEN DATEPART(dw, @year_start_date) = 6 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -5, 2), @year_start_date) /* Friday    */
        WHEN DATEPART(dw, @year_start_date) = 7 
            THEN DATEADD(day,  IIF(@week_of_first_of_Year = 1, -6, 1), @year_start_date) /* Saturday  */
        ELSE @year_start_date
    END

    SET @year_start_date        = DATEADD(week, IIF(@week_of_first_of_Year = 53, 1, 0), @year_start_date) /* If start week is 53 move on one week */
    SET @current_week_int       = CAST(RIGHT(@week_to_increment, 2) AS INT)
    SET @current_week_date      = DATEADD(week, @current_week_int, @year_start_date) 
    SET @new_date               = DATEADD(week, @increment_weeks, @current_week_date)  
    SET @week_Of_Year_Text      = RIGHT('00'+ CAST(DATEPART(ISO_WEEK, @new_date) AS NVARCHAR(2)), 2)
    SET @year_Text              = CONVERT(CHAR(4), DATEPART(YEAR, DATEADD(day, 26 - DATEPART(isoww, @new_date), @new_date)))
    RETURN @year_Text + @week_Of_Year_Text
END
GO

Answer 1

Try with bellow function.

CREATE FUNCTION [dbo].[increment_week] (@yyyyww CHAR(6), @increment_weeks INT)
RETURNS VARCHAR(6)
AS
BEGIN

    DECLARE @StartDatetime DATE;
    DECLARE @yyyy CHAR(4) = LEFT(@yyyyww,4);
    DECLARE @ww TINYINT= RIGHT(@yyyyww,2);

    SET @StartDatetime = CAST(@yyyy AS DATETIME) - DAY(CAST(@yyyy  AS DATETIME)) + 1 + (@ww - 1) * 7;

    SET @StartDatetime =  DATEADD(WW, @increment_weeks, @StartDatetime);

    RETURN CONCAT(DATEPART(YEAR, CONVERT(varchar(10), @StartDatetime,120)), RIGHT(CONCAT('00', DATEPART(WEEK, CONVERT(VARCHAR(10), @StartDatetime,120))), 2))

END
GO

PRINT dbo.increment_week('202001',2)

--DROP FUNCTION [dbo].[increment_week]

Answer 2

You can try something as follow in order to build your week movement:

CREATE FUNCTION dbo.increment_week(@week_to_increment int, @increment_weeks int)
RETURNS int 
AS 
BEGIN
  DECLARE @RetVal int;

  DECLARE @year INT = @week_to_increment/100
  DECLARE @CW INT = @week_to_increment%100

  SET @CW = @CW -1;    

  DECLARE @d DATE = CONVERT(DATE, CAST(@year * 10000 + 101 AS NVARCHAR(8)), 112)
  SET @d =  DATEADD(DAY, @CW*7, DATEADD(d, DATEDIFF(d, '1900-01-6', @d)%7, @d))
    
  DECLARE @MovedWeek DATE = DATEADD(DAY, @increment_weeks * 7, @d)

  SET @RetVal = DATEPART(YEAR, @MovedWeek) * 100 + DATEPART(iso_week, @MovedWeek);
  RETURN @RetVal;
END
GO

A short description of the idea:

• you get January 1st of your year
• you move to the first sunday (or monday - depending if you count sunday as first or as last day of the week)
• you then add your initial week-1 to this date (-1 since you already are in week 1 after step 2)
• afterwards you jump up or down the number of weeks defined in MoveWeeks