Putting month and year together without adding 5

[UnsolvedCoding]

Hey all,

So, I am trying to get the one digit month and the 2 digit year together in a string to search for specific information. I can get both a one digit month

Select Month(getdate())

and the two digit year

Select Right(Year(getdate()),2)

But I can't get them to go together like 311 where 3 is the month and 11 is the year.

I tried using + but it keeps adding them instead of putting them in sequence. I tried cast and convert but the same thing happens.

Ideas?

 

[SQLBill]

You need to convert both values to a character value (VARCHAR or CHAR), then concatenate them, then if needed convert them back to INT.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[UnsolvedCoding]

This

Select Cast(Month(GETDATE() AS VarCHAR(2)))

Gives this

Incorrect syntax near the keyword 'AS'.


But this

Select Cast(GETDATE() AS VarCHAR(3))

Gives this

Aug


So are you saying that I need to convert AUG to 8?

 

[bborissov]

What if the month has two digits?
12,11?

SELECT CAST(MONTH(GETDATE()) as varchar(2))+
       RIGHT(CAST(YEAR(GETDATE()) as varchar(4)),2)
/code]

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Qik3Coder]

Select Cast(Month(GETDATE()[!])[/!] AS VarCHAR(2))

Declare @dt datetime
Select @dt = GetDate()
Select right('0' + rtrim(DATEPART(mm,@dt)), 2)+right(rtrim(DATEPART(yy,@dt)),2)

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[SQLBill]

Or....

SELECT CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE())) + CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[gmmastros]

If you multiply the month by 100 and use the modulus operator on the year and add them together, you should get the results you are looking for.

Ex:

Select Month(GetDate()) * 100 + Year(GetDate()) % 100

Note that the code shown above will return an integer. Running it today you get 811. If you want this to be a string, you'll need to convert it to varchar.

The modulus operator (%) returns the remainder of a division. So 5 % 3 = 2 because 5 / 3 = 1 and [!]2[/!]/3.

2011 % 100 = 11 because 2011 / 100 = 20 and [!]11[/!]/100

So...
[tt]
month * 100 + year % 100
8 * 100 + 2011 % 100
800 + 11
811
[/tt]

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

GMM,
That is quite a trick.
I wouldn't (and didn't) look at the problem that way.

Lod

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[SgtJarrow]

That answer is cool...That's thinking quite a bit outside the box.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer