0

I want to compare 2 dates and use CASE in WHERE to implement this logic: if the end date is larger than 2016-06-30, the end date should be 6/30/2016.

However, I'm encountering an error. The error message is

Missing keyword

My code is below:

    select start_date, end_date, amount from info
    where
       case end_date
       when to_char(end_date, 'yyyy-mm-dd') > '2016-06-30' then to_date(to_char('2016-06-30'), 'M/D/YYYY') as end_date
    end
    order by end_date asc

Can you help me?

2

You could try the following to achieve the result. Your date format string is invalid and the column alias should be placed after the END clause of CASE expression.

SQL> desc info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_DATE                                         DATE
 END_DATE                                           DATE
 AMOUNT                                             NUMBER

SQL> select * from info;

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 14-SEP-16       1200
14-SEP-16 30-JUN-16       1300
30-MAY-16 30-JUN-16       1300
14-SEP-16 20-JUL-16       1400

SQL> select start_date,
case 
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06-30-2016', 'MM/DD/YYYY') end as end_date, amount from info 
order by end_date asc;  2    3    4  

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16                 1300
14-SEP-16                 1300

OR

SQL> select start_date,
case 
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06/30/2016', 'MM/DD/YYYY') else end_date end as end_date, amount from info 
order by end_date asc;  2    3    4  

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16 30-JUN-16       1300
14-SEP-16 30-JUN-16       1300

SQL> 
  • no, my problem is the end date need to change. If the end date is larger than '2016-06-30', the end date should be '6/30/2016'. – betty Sep 14 '16 at 7:37
  • the second SQL is what I wanted!! Thanks! – betty Sep 23 '16 at 7:51
0
select start_date, end_date, amount from info
    where
       case end_date
       when to_char(end_date, 'yyyy-mm-dd') > '2016-06-30' then to_date(to_char('2016-06-30'), 'M/D/YYYY') as end_date
    end
    order by end_date asc

Something isn't right in this Your using the case in the where clause but I don't think that's where you want it. if seems like you just want to format the date

Your not actually comparing the end date to anything which is where you're missing something (it is expecting there result of your case statement to be compared to something)

I think this is what you're trying to do

SELECT start_date
    ,CASE WHEN to_char(end_date, 'yyyy-mm-dd') > '2016-06-30'
        THEN to_date(to_char('2016-06-30'), 'M/D/YYYY') 
        ELSE end_date END
        AS end_date
    ,amount 
FROM info
ORDER BY end_date ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.