×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Putting month and year together without adding
5

Putting month and year together without adding

Putting month and year together without adding

(OP)

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?

RE: Putting month and year together without adding

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: What should I know before I post?

RE: Putting month and year together without adding

(OP)
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?

 

RE: Putting month and year together without adding

What if the month has two digits?
12,11?
[code]
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.

RE: Putting month and year together without adding

CODE

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

CODE

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

RE: Putting month and year together without adding

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: What should I know before I post?

RE: Putting month and year together without adding

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

CODE

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...

month * 100 + year % 100
8     * 100 + 2011 % 100
800         + 11
811


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

RE: Putting month and year together without adding

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

RE: Putting month and year together without adding

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
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close