tonMachine100
asked on
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:
any help is appreciated
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/2014any help is appreciated
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http:#a40478369 doesn't account for the special leap day (Feb 29) logic
i guess my solution above should be revised :)
ADD_MONTHS(issued_date,
(
case when trunc(issued_date,'year')>
then (to_number(to_char(issued_
else (to_number(to_char(issued_
end
)
ADD_MONTHS(issued_date,
(
case when trunc(issued_date,'year')>
then (to_number(to_char(issued_
else (to_number(to_char(issued_
end
)
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
but my original post doesn't calculate the correct years so - still some work to do here
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 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) xwith 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.SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the one I posted does not have such issues :)
date_add(feb_29, 12) >>> gives you a valid date
date_add(feb_29, 12) >>> gives you a valid date
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.
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.
ASKER
thats great - thankyou both for your help
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
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
ADD_MONTHS(issued_date,
(
to_number(to_char(issued_d
) * 12
)