Two Digit Dates in SQL – MM/DD/YYYY

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:


Select Convert(varchar(10), Day(GETDATE())) as OneDigit

Results = “6”

Add the right function with a string zero looks like this query:


Select Right(‘0’+Convert(varchar(10), Day(GETDATE())),2) as TwoDigit

Results = “06”

Another example without the date.


Right(‘0’+ Convert(varchar(3), ROW_NUMBER() over(Order by COLUMN_NAME)),2) as ColNumber


where TABLE_NAME = ‘Product’


This entry was posted in Syndication, T-SQL and tagged . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s