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;

SELECT DISTINCT
CAST(MONTH(TimeByDay) AS VARCHAR(2))

FROM
MSP_EpmAssignmentByDay_UserView

To correct this, the formula used was this;

SELECT DISTINCT
RIGHT(‘000’+CAST(MONTH(TimeByDay) AS VARCHAR(2)),2)

FROM
MSP_EpmAssignmentByDay_UserView

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s