Year format in Case statemnet

General
Highlighted

Year format in Case statemnet

Hi All,

 

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.

 

4 REPLIES
Teradata Employee

Re: Year format in Case statemnet

try single quotes around the dates. And make sure they are in this format.

Re: Year format in Case statemnet

Hi David, Thanks for your quick reponce.

 

I tried single quotes around dates but getting the error : Select Failed .[3535].A character string failed conversation to a numeric value. Apld_Dte feild stored as date as below format:

5/20/2010
4/2/2007
4/13/2007
1/31/2007

 

CASE
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

Senior Supporter

Re: Year format in Case statemnet

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...

 

Ulrich

Junior Contributor

Re: Year format in Case statemnet

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))