MS-access Pass-Through Query to SQL Server 2008 Adding Prefix

Question

I am hoping someone can point me in the right direction with this. I am relatively new to using pass-through queries but think I have a grip of the basics, I have come across a stumbling block however when trying to add a prefix to query result

I have a select query which includes a line to convert a date into the financial year i.e 01/01/2018 would return 2017 as the result using the code below:

    [Created FY] = (CASE WHEN Month(create_date) IN (1, 2, 3) THEN DatePart(year,create_date)-1 ELSE DatePart(year,create_date) END),

I would like to add a prefix to the result so that it would read FY2017. The pass-through is running on SQL Server 2008. I have researched and so far have not come up with any resolutions.

Any help with this conundrum would be greatly appreciated.

Answer 1

+ concatenates strings, but numbers must be converted first.

As there is a MONTH() function, there is YEAR(), to simplify the code a bit.

So:

[Created FY] = 'FY' + CONVERT(char(4),
    (CASE WHEN MONTH(create_date) IN (1, 2, 3) 
     THEN YEAR(create_date)-1 
     ELSE YEAR(create_date) END)
   ),