Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...