MS-access Pass-Through Query to SQL Server 2008 Adding Prefix
Clash Royale CLAN TAG#URR8PPP
MS-access Pass-Through Query to SQL Server 2008 Adding Prefix
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.
1 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.
MONTH()
YEAR()
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)
),
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Thanks for your help Andre, worked a treat. Much appreciated
– Stuey1964
Aug 11 at 17:48