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:

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

Advertisements
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:

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