Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle sql dates

Posted on 2014-12-03
11
Medium Priority
?
150 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 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40478345
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 61

Expert Comment

by:HainKurt
ID: 40478369
try this:

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

Expert Comment

by:sdstuber
ID: 40478381
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!

 
LVL 61

Expert Comment

by:HainKurt
ID: 40478422
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 74

Expert Comment

by:sdstuber
ID: 40478448
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40478460
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 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 40478477
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 61

Expert Comment

by:HainKurt
ID: 40478508
the one I posted does not have such issues :)

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

Expert Comment

by:sdstuber
ID: 40478525
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
ID: 40478563
thats great - thankyou both for your help
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 40478603
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

886 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