Stack Overflow Asked on December 23, 2021
I am trying different ways to put a 0 in front of month less than 10.
I tried the following expression but the 0 get dropped.
What am I doing wrong?
CASE
WHEN month([Transact_Date]) < 10
THEN CONCAT(str(0),STR(month([Transact_Date]),1))
ELSE month([Transact_Date])
END AS month_w_0
Thanks!
Tom
I have tested this and it returns the result you want:
select CASE WHEN mnt < 10 THEN
concat('0' , mnt)
ELSE mnt
END AS month_w_0
from ( select month([Transact_Date]) mnt
from test_t) A
I have realised what your problem is. YOu can do it this way too:
SELECT
CASE
WHEN month([Transact_Date]) < 10
THEN CONCAT(str(0),STR(month([Transact_Date]),1))
ELSE STR(month([Transact_Date]),1)
END AS month_w_0
from test_t
The problem is only the else part and I believe that is because case when clause returns only one type od data. In your then part you have tryed to retunr string and in the else part number.
Hope this helps...
Answered by VBoka on December 23, 2021
I think a left padding trick is what you want here. Assuming your database be SQL Server:
SELECT RIGHT('00' + STR(MONTH([Transact_Date])), 2) AS month_w_0
FROM yourTable;
You don't need a CASE
expression for this. In more recent versions of SQL Server, the FORMAT
function might also be able to handle this.
Answered by Tim Biegeleisen on December 23, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP