Convert Month to Text ... Pad 0 if single digit 3

[JayKusch]

OK ... I know I have seen this trick several times over the course of the years BUT I cannot find it doing a search. Here is what I have ...

DECLARE @Month Char(2)

SET @Month = CONVERT(Char,DATEPART(Month,GetDate()))

Of course when I run this today, I am returned '1' for Jan. No Problem but I need it to be '01'.

What are the string manipulation commands that will convert this for me. Thanks!

Thanks

J. Kusch

 

[JohnDTampaBay]

Here is one way:

DECLARE @Month Char(2)

SET @Month = CASE
WHEN Month(GetDate()) < 10
THEN '0' + CONVERT(Char,DATEPART(Month,GetDate()))
ELSE CONVERT(Char,DATEPART(Month,GetDate()))
END

--OR--

DECLARE @Month Char(2)

SET @Month = RIGHT('0'+CONVERT(Char,DATEPART(Month,GetDate())),2)




--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[JayKusch]

Hey Angel ...

The CASE statement worked BUT the RIGHT\CONVERT returns blanks.



Thanks

J. Kusch

 

[nikhilparchure]

I dont know if there is format the way!!! you want it to be..

But you might be looking at the Stuff function.
I hope that can do the trick for you

Parchure Nikhil

 

[nikhilparchure]

Select Left('0'+CONVERT(Char,DATEPART(Month,GetDate())),2)

Corrected Angels Query

 

[RobertT687]

Either LEFT will work, but the CONVERT target needs to be char(2) or varchar(2). Char by itself defaults to only one character.

declare @Month char(2)
SET @Month = LEFT('0'+CONVERT(Char(2),DATEPART(Month,GetDate())),2)

 

[JohnDTampaBay]

Thanks for the assist, nikhilparchure and RobertT687. My fingers are faster than my brain sometimes. Thanks for the star, folks.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[nikhilparchure]

Sorry Guys...

Select Left('0'+CONVERT(Char(2),DATEPART(Month,'Dec 10 2003')),2)

Failed when I took month of December(Two digit number)

I have come up with something that works for both

Select Right(Ltrim(Rtrim(Stuff(CONVERT(Char(2),DATEPART(Month,'Dec 10 2003')) , 1 ,0, '0'))) ,2)

Select Right(Ltrim(Rtrim(Stuff(CONVERT(Char(2),DATEPART(Month,'Jan 10 2003')) , 1 ,0, '0'))) ,2)

Is there a better way of doing this..

Sorry once agin for the erroneous post

Regards
Parchure Nikhil

 

[marsh]

Try:

select case when DATEPART(Month,'Jan 10 2003') > 9 then str(DATEPART(Month,'Jan 10 2003'),2) else '0' + str(DATEPART(Month,'Jan 10 2003'),1) end

Change Jan to Dec -- should still work

 

[LFCfan]

Just a slightly different way here - I like to use this one because I find it easier to remember (same number of 0's in the string as the varchar length you're casting to, and the length of the char you want to end up with)

-- left pad a month number with zeros
declare @date datetime
declare @month char(2)
set @date = cast('20040117' as datetime)

set @month =  right('00'+ cast(datepart(month, @date) as varchar(2)),2)
print @month

-- left pad a number with zeros, so that total length is 10
declare @int int
declare @char char(10)
set @int = 12345

set @char = right('0000000000'+ cast(@int as varchar(10)),10)
print @char

Hope this helps

 

[RobertT687]

Try:
declare @Month varchar(2)
set @Month = right('0' + CONVERT(varChar(2),DATEPART(Month,'Dec 10 2003')),2)
select @Month

Worked for any month.
note change from char(2) to varchar(2)

 

[r937]

how about yet another solution

[tt]select substring( '010203040506070809101112'
, month(datefield)*2-1
, 2
)[/tt]





rudy

http://r937.com/

SQL Consulting