How to add a Letter to datepart

[Cpreston]

Hi

I have a line of code which works ok

CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - '+ CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QTRYEAR

This gives me the result 4 - 2023 however I would like it to look like Q4 - 2023

I have tried to add the Q in like this and other variations, probably not the right way to do it , but get result like 4Q - 2023

CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + 'Q'+ ' - ' + CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QTRYEAR,

Any advice would be appreciated.

Thanks

 

[bborissov]

Put 'Q' BEFORE DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4))

CAST('Q'+DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - ' + CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QTRYEAR,

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cpreston]

Hi

Thanks for the reply, I have to code now like you suggest

CAST('Q' + DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - ' + CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QtrYear

However I get an error

Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'Q' to data type int.

Thanks

 

[Cpreston]

Hi

I found the solution and this now gives me Q4 - 2023

CONVERT(CHAR(9),'Q' + CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - ' + CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)))AS QtrYear

Thanks for the reply

 

[bborissov]

or

'Q'                                                                 +
    CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(1)) +
    ' - '                                                           +
    CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QtrYear

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

If:
CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - '+ CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QTRYEAR

gives you 4 - 2023, then:

[highlight #FCE94F]'Q' +[/highlight] CAST(DATEPART(qq, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) + ' - '+ CAST(DATEPART(yy, dbo.JourneyHeader.JourneyDate) AS VARCHAR(4)) AS QTRYEAR

will give you [highlight #FCE94F]Q[/highlight]4 - 2023


---- Andy

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