mondaymorning mondaymorning - 3 months ago 10
SQL Question

SQL Server CASE WHEN ELSE STATEMENT not working as should

I am struggling with why the following case statement is not working.

What I want to do is convert the month from a date ("date of death") into two characters so that January becomes 01, February becomes 02 and October to December remain as their two character lengths

However, the code seems to be stumping me

SELECT
LEN(MONT(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH)) AS length_of_month,
(CASE
WHEN LEN(MONTH(CAST(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH as VARCHAR(20)))) = 1
THEN CONCAT('0', MONTH(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH))
END) AS DERIVED_MOD
FROM
RAW.dbo.ONS_Death_DOD201516FYQ1


The above works and converts those with 1 digit to 2 but when I try to implement the second option below it does not seem to work.

SELECT
LEN(MONTH(ONS.DATE_OF_DEATH)) as length_of_month,
(CASE
WHEN LEN(MONTH(CAST(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH as VARCHAR(20)))) = 2
THEN MONTH(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH)
WHEN LEN(MONTH(CAST(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH as VARCHAR(20)))) = 1
THEN CONCAT('0', MONTH(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH))
END) AS DERIVED_MOD
FROM
RAW.dbo.ONS_Death_DOD201516FYQ1


Any help is much appreciated.

Answer

To get the two digit month, just pad with zero, like this:

RIGHT('0' + RTRIM(MONTH(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH)), 2)

No need of CASE WHEN.

The RTRIM is just there to convert the month number to a string.

If your version is SQL Server 2012 or higher, there is even a shorter way with format:

FORMAT(RAW.dbo.ONS_Death_DOD201516FYQ1.DATE_OF_DEATH, 'MM')