Convert day to 2 digits

[nelco]

I want to get the day as 2 digits. For example if I do this:

Select day(getdate())

It returns

1

Whereas I want it to return

01

Any idea on how can this be done.
Thanks in advance!

 

[gmmastros]

Select Right('00' + Convert(VarChar(2), Day(GetDate())), 2)

The problem here is with the Day function itself. You see, it returns an integer. If you want the value to be 2 digits (padded on the left with zero's), you need to use a string type (like varchar).

So...

1. convert to varchar
2. put 0's on the left
3. take the 2 rightmost characters.

Ex:

1
'00' + '1'
Right('001', 2) = '01'

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Select RIGHT('0' + CONVERT(VARCHAR(2), day(getdate())), 2)

 

[r937]

SELECT CONVERT(CHAR(2),GETDATE(),103)



r937.com | rudy.ca

 

[SimonEireconsulting]

In T-SQL I would use CAST instead of CONVERT:

SET @Day = SUBSTRING('0' + CAST(DAY(GetDate()) AS varchar(2)), 1, 2)

 

[RiverGuy]

In T-SQL I would use CAST instead of CONVERT

Usually, when offering contrary advice, you would provide an explanation.

 

[gmmastros]

Or this...

Select Parsename(Convert(VarChar(20), GetDate(), 102), 1)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

SimonEireconsulting,

Your code doesn't work. Take a look at this:

Declare @DateToTest DateTime
Set @DateToTest = '20081225'

select @DateToTest, Day(@DateToTest), SUBSTRING('0' + CAST(DAY(@DateToTest) AS varchar(2)), 1, 2)

Since it's your code, I'll leave it up to you to fix. Unless you want me to show you how. If so, let me know.

[hide]
Declare @DateToTest DateTime
Set @DateToTest = '20081215'

select @DateToTest, Day(@DateToTest), SUBSTRING('0' + CAST(DAY(@DateToTest) AS varchar(2)), Len(CAST(DAY(@DateToTest) AS varchar(2))), 2)
[/hide]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SimonEireconsulting]

RiverGuy,

Explanation:
1. I am sure I read some time ago in some MS documentation that the preferred way of converting data types was to use CAST (now I can't find where I read that, although I think was in Books Online). Also, if you look at CAST and CONVERT in Books Online the majority of the examples use CAST

2. Using CAST you don't have to worry about or know any of the conversion styles when dealing with datetimes - gmmastros has already given 2 different codes in the examples posted above:

Convert(VarChar(2), Day(GetDate())) - No Code and
Convert(VarChar(20), GetDate(), 102) - Code 102

 

[SimonEireconsulting]

I beg your pardon gmmastros - my code does work. Try pasting the code below in a new query window:

DECLARE @Day varchar(2)

SET @Day = SUBSTRING('0' + CAST(DAY(GetDate()) AS varchar(2)), 1, 2)

PRINT @Day

-- note the the second line above is exactly as posted

When you execute this code the result is:
01

 

[gmmastros]

SimonEireconsulting,

Wait 10 days and try again.

On December 10th, DAY will return 10. You concatenate that with 0 to get '010', and then substring it to effectively get the first 2 characters, resulting in '01' instead of '10'.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

maybe y'all missed this the first time around

probably because it's so concise and neat

SELECT CONVERT(CHAR(2),GETDATE(),103)



r937.com | rudy.ca

 

[SimonEireconsulting]

yes, yes, yes - there is an error in my code. Correct code would be:

SET @Day = SUBSTRING('0' + CAST(DAY(GetDate()) AS varchar(2)), LEN(CAST(DAY(GetDate()) AS varchar(2))), 2)

OR

SET @Day = RIGHT('0' + CAST(DAY(GetDate()) AS varchar(2)), 2)

Either way, my reason for posting my example (albeit code with a bug) was to demostrate the use of CAST rather than CONVERT

 

[gmmastros]

Rudy,

For what it's worth, your suggestion is clearly the 'winner' here. It's concise, it's neat, it always works.

Kudos to you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

maybe y'all missed this the first time around

I saw it, and yes, I agree it's a good solution, the most concise out of the bunch.

 

[RiverGuy]

In case the poster comes back, use Reply #3.

 

[SimonEireconsulting]

r937 - I saw the post too.

gmmastros, riverguy: the most concise is not always the "best"

CONVERT(char(2), GetDate(), 103) relies on the the date being truncated to fit a char(2) variable, and having to know to use the code 103.

Personally I don't like truncating data.