Month should be 2 digits

Question

Post reply

Month should be 2 digits

Viewing 8 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Tad Right there with Babe Points: 751 More actions · Answer 1

Add this to the very end of your script and run it.

print right('00' + @tmonth,2)

cafemar1 SSCrazy Points: 2405 More actions · Answer 2

Thank you. I added the print statement you recommended and it came back the same. What am I doing wrong?

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

you can just add this select statement to your variable

select @Month = right(('0' + cast(datepart(mm, <yourdate>) as varchar(2), 2)

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Here try this:

select @Temp as '@Temp',@T1 as '@T1', substring(cast(100+month(@t1)as char(3)),2,3) as 'month1' , substring(cast(datepart(mm,@t1)+100 as char(3)),2,3) as 'month2'

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Antares686 SSC Guru Points: 125444 More actions · Answer 5

The problem comes from the fact that month() returns an int not a char, as does datepart. You will have to cast to a char to get it to work like you want.

Other option are

select replicate('0', 2 - len(month(@t1))) + cast(month(@t1) as varchar(2)) as x

or

DECLARE @m1 AS VARCHAR(2)

SET @m1 = cast(month(@t1) as varchar(2)) -- do this explicitly as SQl versions don't always handle the same and may not in the future.

select replicate('0', 2 - len(@m1)) + @m1 as x

Also, being that you are using SQL 2000 it might be more helpfull to write whatever you decide on into a UDF (User Defined Function) that way you can reuse with much less code.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 6

quote:
Here try this:
select @Temp as '@Temp',@T1 as '@T1', substring(cast(100+month(@t1)as char(3)),2,3) as 'month1' , substring(cast(datepart(mm,@t1)+100 as char(3)),2,3) as 'month2'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

I used to do this all the time back in the old assembler programming days. It works well and is obvious once you start thinking RIGHT instead of left.

David Burrows SSC Guru Points: 65144 More actions · Answer 7

I would use

SELECT @TMonth = REPLACE(STR(MONTH(@T1),2,0),' ','0')

but then thats me.

Far away is close at hand in the images of elsewhere.
Anon.

cafemar1 SSCrazy Points: 2405 More actions · Answer 8

Thank you all. With all your input, I solve the issue and successfully implementd it. Thank you again.