Solved

oracle sql dates

Posted on 2014-12-03
11
141 Views
Last Modified: 2014-12-03
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
Comment
Question by:tonMachine100
  • 6
  • 4
11 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
try this:

ADD_MONTHS(issued_date,
(
to_number(to_char(issued_date,'yyyy'))-to_number(to_char(sysdate,'yyyy'))
) * 12
)
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
http:#a40478369  doesn't account for the special leap day (Feb 29) logic
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
Comment Utility
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
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
the one I posted does not have such issues :)

date_add(feb_29, 12) >>> gives you a valid date
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Closing Comment

by:tonMachine100
Comment Utility
thats great - thankyou both for your help
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now