sqlconcatenationcase-statementgetdatedatepart

CONCAT leading 0 in case statement

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?

Solution

  • case expressions return a single value and that has a single type. If any of the return values are numbers, then the return value is a number, not a string.

    To get a string, use datename():

    (CASE WHEN DATEPART(WW, getdate()) < 10 
      THEN CONCAT('0', DATENAME(WW,getdate())) 
      ELSE DATENAME(WW, getdate()) 
 END)

    Or you could simplify your logic:

    RIGHT(CONCAT('0', DATENAME(WW, getdate()), 2)

    Or simplify everything. For instance, a number might be sufficient:

    YEAR(GETDATE()) * 100 + DATEPART(WW, getdate())