General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-12-2017
11:14 AM

09-12-2017
11:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-12-2017
11:42 AM

09-12-2017
11:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-12-2017
12:07 PM

09-12-2017
12:07 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-12-2017
12:19 PM

09-12-2017
12:19 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-12-2017
12:39 PM

09-12-2017
12:39 PM

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