Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert day to 2 digits

Status
Not open for further replies.

nelco

Programmer
Apr 4, 2006
93
US
I want to get the day as 2 digits. For example if I do this:
Code:
Select day(getdate())
It returns
Code:
1
Whereas I want it to return
Code:
01
Any idea on how can this be done.
Thanks in advance!
 
Code:
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
 
Select RIGHT('0' + CONVERT(VARCHAR(2), day(getdate())), 2)
 
In T-SQL I would use CAST instead of CONVERT:

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

Code:
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
 
SimonEireconsulting,

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

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

 
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
 
SimonEireconsulting,

Wait 10 days and try again. [wink]

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
 
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
 
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
 
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
 
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.
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top