When creating dates or numbers as strings it is sometimes required to have two digit numbers.
For example, instead of this: 3/1/2011
You want to see this: 03/01/2011
There is an easy way to do this using the Right() function and adding a string zero to the front of a number, and then take the right two characters.
Here are some examples:
Let’s say the Date is December 6, 2010, so the day is a single digit “6”
Running this query:
1 | Select Convert(varchar(10), Day(GETDATE())) as OneDigit |
Results = “6”
Add the right function with a string zero looks like this query:
1 | Select Right(‘0’+Convert(varchar(10), Day(GETDATE())),2) as TwoDigit |
Results = “06”
Another example without the date.
select COLUMN_NAME,
Right(‘0’+ Convert(varchar(3), ROW_NUMBER() over(Order by COLUMN_NAME)),2) as ColNumber
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘Product’
Order by COLUMN_NAME
