Let’s say in one field there is the "year" as an integer 2010, and in another field is the "month" as an integer 11. How can you concatenate them and not add them?

Essentially the result I'm looking for based on the example would be this: 201011 but I still want this to be an integer and not a string.

Thank you!

DECLARE @Y INT,@m INT SET @Y = 2000 SET @m = 11 SELECT CAST(CAST(@Y AS VARCHAR(4)) + CAST(@m AS VARCHAR(12)) AS INT)

Thanks Abdallah, but what if the Month was 4 and I wanted a zero infront of it so that the month component was always two digits.

Like this: 200004

Thanks

You can use the RIGHT function, as follows

DECLARE @Y INT,@m INT
SET @Y = 2000
SET @m = 1
SELECT CAST(CAST(@Y AS VARCHAR(4)) + RIGHT('0' + CAST(@m AS VARCHAR(12)),2) AS INT)

Abdallah, you the man!

Thanks buddy!

Glad to be of assistance. 

But now I think about it, there is no need to add the outer CAST since the optimizer will do an implicit casting back to int. So just do the following

DECLARE @Y INT,@m INT
SET @Y = 2000
SET @m = 4
SELECT CAST(@Y AS VARCHAR(4)) + RIGHT('0' + CAST(@m AS VARCHAR(12)),2)

Even better, it works perfectly.

This is fairly easy in VB or .Net but I'm somewhat new to T-SQL and it helps to have such a great and responsive community to help at times like this.

There is nothing like SQL and t-sql. You will love it once you start working on it, and you will find a tremendous help on this forum.

Another way to skin the cat:

SELECT str(year * 100 + month), 6)

To pad with zeroes in general you can do:

DECLARE @number int
SELECT @number = 4711
SELECT replace(str(@number, 8), ' ', '0')

Erland is on the right track, it you still want the result to be an integer

100 * Year + Month will give you the result you want.

 

• Proposed as answer by Badii Gharbi Saturday, November 06, 2010 6:58 PM
• Marked as answer by Daniel_DeHaven Saturday, November 06, 2010 10:39 PM

Abdallah,

I got the following error from the code above

CAST or CONVERT: invalid attributes specified for type 'varchar'

My appologies but I'm just not familiar enough with Cast to debug it properly.

 

Hi Peso,

This one works perfectly, I like the simple logic of the solution...let the tricks of math do the work for you.

Thanks,

Dan

Can you post the code that generated the error?

Abdallah,

My bad, I had typo'd it into another query.

It works just as it should.

Thank you again!

 

Worked for me. Thanks.