• Hello again and thank you for your continued interest in this in advance. I want to clarify a couple of things real quick in the interests of better understanding. The system I am currently (and hopefully somewhat temporarily) saddled with maintaining uses a procedural process to generate a material identifier, a portion of which is a fixed format "YYWK" representing the last 2 digits of the year and another 2 for the week of the year. Our process is driven by a weekly order cycle, currently with a Monday date representing the order week, but could be designated as any day of the week. This date drives the population of the "YYWK", which is the same for every ID for that week. If the week is the first week of the year, the year value should reflect the year that Jan 1st is in, not necessarily the year of the date given. I did create and post what for now is an acceptable solution, but for both academic interest and potential future improvement I wanted to take this further.

    The date data to drive this could be any date, forgive my awkward usage of a CTE (which was fun) to create the test data.

    IF OBJECT_ID('TempDB..#TestTable','U') IS NOT NULL

    DROP TABLE #TestTable

    CREATE TABLE #TestTable

    (

    CalendarKey INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    CalendarDate DATETIME,

    ISO_YearWeek VARCHAR(4),

    Adj_YearWeek VARCHAR(4)

    )

    SET IDENTITY_INSERT #TestTable ON

    ;WITH tDates AS

    (

    SELECT 1 AS CalendarKey,

    CAST('12/26/1999' AS DATETIME) AS CalendarDate,

    NULL AS ISO_YearWeek,

    NULL AS Adj_YearWeek

    UNION ALL

    SELECT CalendarKey + 1,

    CalendarDate + 1 AS CalendarDate,

    NULL AS ISO_YearWeek,

    NULL AS Adj_YearWeek

    FROM tDates

    WHERE CalendarDate < '1/4/2020'

    )

    INSERT #TestTable

    (

    CalendarKey,

    CalendarDate,

    ISO_YearWeek,

    Adj_YearWeek

    )

    SELECT CalendarKey,

    CalendarDate,

    ISO_YearWeek,

    Adj_YearWeek

    FROM tDates

    OPTION (MAXRECURSION 7315)

    SET IDENTITY_INSERT #TestTable OFF

    Below find the equivalent of what is in place today (ISO), with the somewhat unsatisfactory replacement (Jan 1st based) to determine the "YYWK" value:

    UPDATE #TestTable

    SET ISO_YearWeek =

    CASE

    WHEN (MONTH(CalendarDate) = 12 AND DATEPART(ISOWW, CalendarDate) = 1)

    THEN RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate) + 1), 2)

    ELSE RIGHT(CONVERT(CHAR(4), YEAR(CalendarDate)), 2)

    END +

    RIGHT('0' + CAST(CONVERT(INT, DATEPART(ISOWW, CalendarDate)) AS VARCHAR), 2)

    UPDATE #TestTable

    SET Adj_YearWeek =

    CASE

    WHEN (DATEDIFF( DD, DATEADD(WEEK,DATEDIFF(WEEK,'18991231', (CAST('1/1/' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR) AS DATETIME) )), '18991231'), CalendarDate )) >= 0

    THEN (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) + 1 AS VARCHAR), 2)) + '01'

    ELSE (RIGHT( '0' + CAST(DATEPART(YYYY, CalendarDate) AS VARCHAR), 2)) + RIGHT( '0' + CAST(DATEPART(WK, CalendarDate) AS VARCHAR), 2)

    END

    SELECT CalendarDate,

    DATENAME(DW,CalendarDate) AS Weekday,

    ISO_YearWeek,

    Adj_YearWeek

    FROM #TestTable

    The results illustrate the wanted value in the "Adj" column, and also enlightened me that even our current ISO method would fail if our weekly order date was changed to a Sunday.

    CalendarDateWeekdayISO_YearWeekAdj_YearWeek

    ----------------------- ------------------------------------------------------

    2008-12-26 00:00:00.000 Friday08520852

    2008-12-27 00:00:00.000 Saturday08520852

    2008-12-28 00:00:00.000 Sunday08520901

    2008-12-29 00:00:00.000 Monday09010901

    2008-12-30 00:00:00.000 Tuesday09010901

    2008-12-31 00:00:00.000 Wednesday09010901

    2009-01-01 00:00:00.000 Thursday09010901

    2009-01-02 00:00:00.000 Friday09010901

    2009-01-03 00:00:00.000 Saturday09010901

    2009-01-04 00:00:00.000 Sunday09010902

    2009-01-05 00:00:00.000 Monday09020902