Format the date from OLAP to transaction table

This is my fiddle

I have a table like this

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:

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:

>Solution :

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.