• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

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
0
tonMachine100
Asked:
tonMachine100
  • 6
  • 4
2 Solutions
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now