Please help me with the below CASE statement:
Case when Applied_Date BETWEEN 07/01/2017 AND 06/30/2018 THEN '2018'
when Applied_Date BETWEEN 07/01/2016 AND 06/30/2017 THEN '2017'
when Applied_Date BETWEEN 07/01/2015 AND 06/30/2016 THEN '2016'
end as Fiscal_Year
But I am getting ? as a result.
Hi David, Thanks for your quick reponce.
I tried single quotes around dates but getting the error : Select Failed ..A character string failed conversation to a numeric value. Apld_Dte feild stored as date as below format:
WHEN Apld_Dte BETWEEN '07/01/2017' AND '06/30/2018' THEN 2018
WHEN Apld_Dte BETWEEN '07/01/2016' AND '06/30/2017' THEN 2017
WHEN Apld_Dte BETWEEN '07/01/2015' AND '06/30/2016' THEN 2016
WHEN Apld_Dte BETWEEN '07/01/2014' AND '06/30/2015' THEN 2015
WHEN Apld_Dte BETWEEN '07/01/2013' AND '06/30/2014' THEN 2014
WHEN Apld_Dte BETWEEN '07/01/2012' AND '06/30/2013' THEN 2013
WHEN Apld_Dte BETWEEN '07/01/2011' AND '06/30/2012' THEN 2012
WHEN Apld_Dte BETWEEN ' 07/01/2010' AND '06/30/2011' THEN 2011
WHEN Apld_Dte BETWEEN '07/01/2009' AND '06/30/2010' THEN 2010
WHEN Apld_Dte BETWEEN '07/01/2008' AND '06/30/2009' THEN 2009
WHEN Apld_Dte BETWEEN '07/01/2007' AND '06/30/2008' THEN 2008
WHEN Apld_Dte BETWEEN '07/01/2006' AND '06/30/2007' THEN 2007
WHEN Apld_Dte BETWEEN '07/01/2005' AND '06/30/2006' THEN 2006
END AS FISCAL_YEAR
beside some issue with the format of the date -
why not doing a plain calucaltion?
case when extract(month from Apld_Dte) <= 6 then extract(year from Apld_Dte) else 'What is your else ????' end as FISCAL_YEAR
and if you really need a char just cast it...
Without quotes 07/01/2017 is a calculation, 7 / 1 / 2017, and as it's based on integers the result is an integer 0.
Then the date is automatially typecasted to int and comparing using xx BETWEEN 0 AND 0.
Better adjust the date to match you fiscal year and then simply use EXTRACT:
EXTRACT(YEAR FROM ADD_MONTHS(Applied_Date, 6))