Format the date from OLAP to transaction table

Question

This is my fiddle

I have a table like this

ID	Period Month	01	02	Employee Code
1	202401	K	L	005678
2	202401	S1	M	005679

With period Month are in yyyymm and 01 02 represents the day of the date.

I want to make the format would be like this

Expected result:

Employee Code	Period Month	Date	Value
005678	202401	2024-01-01	K
005678	202401	2024-01-02	L
005679	202401	2024-01-01	S1
005679	202401	2024-01-02	M

With this query

SELECT 
    [Employee Code], 
    [Period Month],
    -- Menyusun tanggal berdasarkan kolom tanggal (01, 02, ..., 31) sesuai dengan 'Period Month'
    CASE 
        WHEN [Date] = '01' AND TRY_CAST(CAST([Period Month] AS CHAR(4)) + '-01' AS DATE) IS NOT NULL THEN CAST(CAST([Period Month] AS CHAR(4)) + '-01' AS DATE)
        WHEN [Date] = '02' AND TRY_CAST(CAST([Period Month] AS CHAR(4)) + '-02' AS DATE) IS NOT NULL THEN CAST(CAST([Period Month] AS CHAR(4)) + '-02' AS DATE)
    END AS [Date],
    [Value]
FROM 
    (SELECT [Employee Code], [Period Month], 
            [01], [02]
     FROM CTE1) AS SourceTable
UNPIVOT
    ([Value] FOR [Date] IN 
        ([01], [02])) AS UnpivotedTable
ORDER BY [Employee Code], [Period Month], [Date];

But it returns like this:

Employee Code	Period Month	Date	Value
005678	202401	NULL	K
005678	202401	NULL	L
005679	202401	NULL	S1
005679	202401	NULL	M

Answer 1

You are currently trying to CAST 2024-01 into a date - but that isn't a valid date. You need to keep all of [Period Month] and remove the - (because otherwise you have 202401-01 which also isn't valid) so you are then CASTing 20240101 into a date - which is valid. Then you can remove the CASE by directly inserting the [Date] column e.g.

TRY_CAST([Period Month] + [Date] AS DATE)

If you build your desired result up piece by piece its easy to spot at what point its not working as you expect.