I found an error in a query I inherited from a previous coworker that I am trying to fix but it's a weird issue I haven't encountered before.. here is a snip of the original query:
CAST(CONCAT(DATEPART(YYYY,getdate()),
(CASE
WHEN DATEPART(WW,getdate()) < 10
THEN CONCAT('0', DATEPART(WW,getdate()))
ELSE DATEPART(WW,getdate())
END)
) AS INT)
When getdate() = 2021-01-08 10:16:41.440 the query results in
20212
Expected result should be
202102
I found the issue relies in in the CASE statement. When I tried to change the THEN clause to
CAST(CONCAT(DATEPART(YYYY,getdate()),
(CASE
WHEN DATEPART(WW,getdate()) < 10
THEN RIGHT('0'+ CONVERT(VARCHAR(2), DATEPART(WW,getdate())),2)
ELSE DATEPART(WW,getdate())
END)
) AS INT)
I still get
20212
But when I run
SELECT RIGHT('0'+ CONVERT(VARCHAR(2), DATEPART(WW,getdate())),2)
I get
02
Can someone explain this? Why does it work outside of the CASE statement, but not within?