Adding zeros before a number in SQL e.g. Instead of 1, 2, 3 etc, have 01, 02, 03 etc
As per title, I had a client who had a list of months that would not order correctly in SSRS because of a SQL Query for a parameter, as numbers less than 10 needed a zero in front of them to be deemed lower than 10, or 1 would be sat with 10, 2 with 20, 3 with 30 etc – although obviously in this case the numbers didn’t go that high due to being months and thus only up to 12. The formula would work for other scenarios as well though.
Here is the first formula that doesn’t have zeros before the numbers less than 10;
CAST(MONTH(TimeByDay) AS VARCHAR(2))
To correct this, the formula used was this;
RIGHT(‘000’+CAST(MONTH(TimeByDay) AS VARCHAR(2)),2)
This puts the 0 before the numbers less than 10. The formula could be used for more zero’s where required, just adapt the values in the query accordingly.