Joining 2 cast fields together 1

[Cpreston]

I am trying to sort a view out and part of the code as the below which brings back the Calendar Month and year back correctly. I want to join them together so I get something like 1/2020 M/YYYY

I would prefer to have a day but there is no date filed to use. the table only as Year and Month.

So how can I join them together, I have tried using + but cannot get it to work. Below is the part of the query that I want to join together. Thanks

(dbo.[148-vwQuantityUsedCheck_UsageP1].Month AS int) AS CalendarMonth, CAST(dbo.[148-vwQuantityUsedCheck_UsageP1].Year AS int)

 

[Andrzejek]

cannot get it to work" - any errors?

Do you have [tt]Month[/tt] and [tt]Year[/tt] as names of your fields in the view? If so, not a good idea: SQL Reserved Words, see Words to Avoid

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

No errors

What I have done is taken out the cast as I cannot see why it had to be integer.

I then added the fields back in like this and then joined them together

dbo.[148-vwQuantityUsedCheck_UsageP1].Month,
dbo.[148-vwQuantityUsedCheck_UsageP1].Year,
dbo.[148-vwQuantityUsedCheck_UsageP1].Month +'/'+ dbo.[148-vwQuantityUsedCheck_UsageP1].Year As Combined

It joins them together but I have a big gap between them like below, If I could get rid of the gap between them it would work, any ideas. Thanks

12 /2020
2 /2020

 

[Andrzejek]

Just a guess here...
Looks like there is a Space after the Month, so:
[tt]
[blue]Trim([/blue]dbo.[148-vwQuantityUsedCheck_UsageP1].Month[blue])[/blue] +'/'+ dbo...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Thanks for the reply

I have tried both RTRIM and LTRIM and both together but get the same result , any ideas please

RTRIM(LTRIM (dbo.[148-vwQuantityUsedCheck_UsageP1].Month +'/'+ dbo.[148-vwQuantityUsedCheck_UsageP1].Year)) AS Combined

12 /2020

 

[Cpreston]

Hi

Managed it as below, it as given me the format I need , thanks for the help

RTRIM (dbo.[148-vwQuantityUsedCheck_UsageP1].Month) +'/'+ RTRIM(dbo.[148-vwQuantityUsedCheck_UsageP1].Year) AS Combined