oracle sql dates

i have a column issued_date. i need to work out the date of the next instance of that date (based on sysdate - in this instance 03-12-2014). see the sample data below -review_date is the column i need the sql to calculate:

STUD_ID	ISSUED_DATE	review_date
267,239.00	17/07/2008	17/07/2015
260,522.00	16/02/2010	16/02/2015
172,509.00	13/09/2011	13/09/2015
299,229.00	12/01/2012	12/01/2015
123,123.00	29/02/2012	01/03/2015
266,493.00	02/05/2012	02/05/2015
311,849.00	20/03/2013	20/03/2015
228,698.00	28/06/2013	28/06/2015
98,479.00	30/10/2013	30/10/2015
340,817.00	27/02/2014	27/02/2015
355,151.00	06/10/2014	06/10/2015
12,456.00	10/12/2014	10/12/2014
687,987.00	20/12/2014	20/12/2014

Open in new window


any help is appreciated
tonMachine100Asked:
Who is Participating?
 
sdstuberCommented:
incrementing leap-day  (Feb 29) into March 1 for future dates makes it a little tricky, but for normal days it's not too bad

SELECT stud_id,
       issued_date,
         CASE
             WHEN TO_CHAR(issued_date, 'mm-dd') = '02-29'
              AND CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12) > 0
             THEN
                 issued_date + 1
             ELSE
                 issued_date
         END
       + NUMTOYMINTERVAL(CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12), 'year')
           review_date
  FROM yourtable


replace   DATE '2014-12-03'   with TRUNC(SYSDATE) for your normal use
0
 
HainKurtSr. System AnalystCommented:
try this:

ADD_MONTHS(issued_date,
(
to_number(to_char(issued_date,'yyyy'))-to_number(to_char(sysdate,'yyyy'))
) * 12
)
0
 
sdstuberCommented:
http:#a40478369  doesn't account for the special leap day (Feb 29) logic
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
HainKurtSr. System AnalystCommented:
i guess my solution above should be revised :)

ADD_MONTHS(issued_date,
(
case when trunc(issued_date,'year')>trunc(sysdate,'year')
then (to_number(to_char(issued_date,'yyyy'))-to_number(to_char(sysdate,'yyyy'))+1) * 12
else (to_number(to_char(issued_date,'yyyy'))-to_number(to_char(sysdate,'yyyy'))) * 12
end
)
0
 
sdstuberCommented:
http:#a40478422  doesn't address the leap day logic either.  

but my original post doesn't calculate the correct years  so - still some work to do here
0
 
sdstuberCommented:
sorry for the confusion,  My first post DOES work,  I typed in the wrong date in my test,  once I used 2014-12-03 for the sample sysdate   I was able to correctly replicate all of the results

Here's a simple test using the sample data provided

TARGET is the quested review_date
REVIEW_DATE is my result

WITH yourtable
     AS (SELECT stud_id,
                TO_DATE(issued_date, 'dd/mm/yyyy') issued_date,
                TO_DATE(target, 'dd/mm/yyyy') target
           FROM (SELECT 267239.00 stud_id, '17/07/2008' issued_date, '17/07/2015' target FROM DUAL
                 UNION ALL
                 SELECT 260522.00, '16/02/2010', '16/02/2015' FROM DUAL
                 UNION ALL
                 SELECT 172509.00, '13/09/2011', '13/09/2015' FROM DUAL
                 UNION ALL
                 SELECT 299229.00, '12/01/2012', '12/01/2015' FROM DUAL
                 UNION ALL
                 SELECT 123123.00, '29/02/2012', '01/03/2015' FROM DUAL
                 UNION ALL
                 SELECT 266493.00, '02/05/2012', '02/05/2015' FROM DUAL
                 UNION ALL
                 SELECT 311849.00, '20/03/2013', '20/03/2015' FROM DUAL
                 UNION ALL
                 SELECT 228698.00, '28/06/2013', '28/06/2015' FROM DUAL
                 UNION ALL
                 SELECT 98479.00, '30/10/2013', '30/10/2015' FROM DUAL
                 UNION ALL
                 SELECT 340817.00, '27/02/2014', '27/02/2015' FROM DUAL
                 UNION ALL
                 SELECT 355151.00, '06/10/2014', '06/10/2015' FROM DUAL
                 UNION ALL
                 SELECT 12456.00, '10/12/2014', '10/12/2014' FROM DUAL
                 UNION ALL
                 SELECT 687987.00, '20/12/2014', '20/12/2014' FROM DUAL))
SELECT x.*,
       CASE WHEN target != review_date THEN 'Error' END sdstuber_errors,
       CASE WHEN target != hainkurt1 THEN 'Error' END hainkurt1_errors,
       CASE WHEN target != hainkurt2 THEN 'Error' END hainkurt2_errors
  FROM (SELECT stud_id,
               issued_date,
               target,
                 CASE
                     WHEN TO_CHAR(issued_date, 'mm-dd') = '02-29'
                      AND CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12) > 0
                     THEN
                         issued_date + 1
                     ELSE
                         issued_date
                 END
               + NUMTOYMINTERVAL(CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12), 'year')
                   review_date,
               ADD_MONTHS(
                   issued_date,
                     (  TO_NUMBER(TO_CHAR(issued_date, 'yyyy'))
                      - TO_NUMBER(TO_CHAR(DATE '2014-12-03', 'yyyy')))
                   * 12
               )
                   hainkurt1,
               ADD_MONTHS(
                   issued_date,
                   (CASE
                        WHEN TRUNC(issued_date, 'year') > TRUNC(DATE '2014-12-03', 'year')
                        THEN
                              (  TO_NUMBER(TO_CHAR(issued_date, 'yyyy'))
                               - TO_NUMBER(TO_CHAR(DATE '2014-12-03', 'yyyy'))
                               + 1)
                            * 12
                        ELSE
                              (  TO_NUMBER(TO_CHAR(issued_date, 'yyyy'))
                               - TO_NUMBER(TO_CHAR(DATE '2014-12-03', 'yyyy')))
                            * 12
                    END)
               )
                   hainkurt2
          FROM yourtable) x

Open in new window


with results...

Session altered.

   STUD_ID ISSUED_DATE TARGET     REVIEW_DATE HAINKURT1  HAINKURT2  SDSTUBER_ERRORS HAINKURT1_ERRORS HAINKURT2_ERRORS
---------- ----------- ---------- ----------- ---------- ---------- --------------- ---------------- ----------------
    267239 2008-07-17  2015-07-17 2015-07-17  2002-07-17 2002-07-17                 Error            Error           
    260522 2010-02-16  2015-02-16 2015-02-16  2006-02-16 2006-02-16                 Error            Error           
    172509 2011-09-13  2015-09-13 2015-09-13  2008-09-13 2008-09-13                 Error            Error           
    299229 2012-01-12  2015-01-12 2015-01-12  2010-01-12 2010-01-12                 Error            Error           
    123123 2012-02-29  2015-03-01 2015-03-01  2010-02-28 2010-02-28                 Error            Error           
    266493 2012-05-02  2015-05-02 2015-05-02  2010-05-02 2010-05-02                 Error            Error           
    311849 2013-03-20  2015-03-20 2015-03-20  2012-03-20 2012-03-20                 Error            Error           
    228698 2013-06-28  2015-06-28 2015-06-28  2012-06-28 2012-06-28                 Error            Error           
     98479 2013-10-30  2015-10-30 2015-10-30  2012-10-30 2012-10-30                 Error            Error           
    340817 2014-02-27  2015-02-27 2015-02-27  2014-02-27 2014-02-27                 Error            Error           
    355151 2014-10-06  2015-10-06 2015-10-06  2014-10-06 2014-10-06                 Error            Error           
     12456 2014-12-10  2014-12-10 2014-12-10  2014-12-10 2014-12-10                                                  
    687987 2014-12-20  2014-12-20 2014-12-20  2014-12-20 2014-12-20                                                  

13 rows selected.

Open in new window

0
 
sdstuberCommented:
Here's another way to calculate it
Same idea, but uses add_months instead of interval math, and includes the special Feb 29 logic


SELECT stud_id,
       issued_date,
       target,
         ADD_MONTHS(issued_date, CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12) * 12)
       + CASE
             WHEN TO_CHAR(issued_date, 'mm-dd') = '02-29'
              AND CEIL(MONTHS_BETWEEN(DATE '2014-12-03', issued_date) / 12) > 0
             THEN
                 1
             ELSE
                 0
         END
           review_date
  FROM yourtable
0
 
HainKurtSr. System AnalystCommented:
the one I posted does not have such issues :)

date_add(feb_29, 12) >>> gives you a valid date
0
 
sdstuberCommented:
there is no such function as date_add()  in oracle unless you create one.

but, neither of your posts include a call to such a function so even if there was one, it wouldn't apply

if you mean add_months - then adding 12 months to   Feb 29  will yield Feb 28 of the following year,  NOT Mar 1 as requested.


I included both of your results in the post above to show what was happening.
If I've broken your code somehow in the test case, I apologize and please correct my mistake.

I'm basing all my comments of correct vs not correct based on the test I've shown above.

If you see a hole in my testing, please point it out and I'll do my best to post a correction.
0
 
tonMachine100Author Commented:
thats great - thankyou both for your help
0
 
HainKurtSr. System AnalystCommented:
sdstuber,

date_add(feb_29, 12) >>> gives you a valid date
>>> should be
add_months(feb_29, 12) >>> gives you a valid date

ok, i messed up my posts, since I did not have access to oracle db at that time...

Here is my revised solution (no check for feb 29 logic here)

CASE
WHEN to_char (issued_date, 'MMDD') < to_char (sysdate, 'MMDD')
THEN (  TO_NUMBER (TO_CHAR (sysdate, 'yyyy')) - TO_NUMBER (TO_CHAR (issued_date, 'yyyy'))  + 1)  * 12
ELSE (TO_NUMBER (TO_CHAR (sysdate, 'yyyy')) - TO_NUMBER (TO_CHAR (issued_date, 'yyyy'))) * 12
END
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.