Concatenation

[NotSQL]

Can anyone help, im trying to concatenate data together. However im not getting the correct results.

What I have is a productiondate which I want to take the year and month and concatenate together.

eg

200602
200512
200401

I've tried:

select cast(year(productiondate as varchar)) & cast(month(productiondate as varchar))
from table1

What am i doing wrong?

 

[iaresean]

try using a + not a &

select (cast(year(productiondate as varchar)) + cast(month(productiondate as varchar))) AS yearmonthcat
from table1

Sean.

 

[vongrunt]

How 'bout [!]convert(varchar(6), datecolumn, 112)[/!]?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[eichmat]

select cast(year(productiondate as varchar)) & cast(month(productiondate as varchar))
from table1

First, forgot the close-parenthesis after "productiondate" in the YEAR and MONTH function calls and second follow sean4e's advice about the "+" instead of "&" (which is used to do a logical order, not concatenation).

select cast(year(productiondate) as varchar(4)) + cast(month(productiondate) as varchar(2)) + cast(day(productiondate) as varchar(2))
from table1

If you choose vongrunt's solution, make the varchar size 8 {convert(varchar(8), productiondate, 112)}, otherwise you'll only get back year and month (e.g. convert(varchar(6), getdate(), 112) returns "200603" for today...).

The convert-112 will give you leading zeros for the month and day values, so if you don't want them, use the first code in blue, otherwise, the convert-112 is cleaner and more efficient.


-Tim