?
Solved

Oracle Year/Month/Day Difference between two columns

Posted on 2013-01-11
4
Medium Priority
?
4,402 Views
Last Modified: 2013-01-11
I found this SQL on the web - thanks to whomever wrote it.  But as you can see from my dates, it's not correctly working.  Is it because of leap years?  Any thoughts, comments, direction greatly appreciated.  Thanks.


select ID, BIRTH, MEETING_DATE,
trunc(months_between(MEETING_DATE,BIRTH)/12) year,
    trunc(mod(months_between(MEETING_DATE,BIRTH),12)) month,
   trunc(sysdate-add_months(BIRTH,trunc(months_between(MEETING_DATE,BIRTH)/12)*12+trunc(mod(months_between(MEETING_DATE,BIRTH),12)))) day
   from ica_data
/



ID           BIRTH     MEETING_DATE     YEAR      MONTH        DAY
------------------------- --------- --------- ---------- ---------- ----------
8900007      01-JAN-08 29-NOV-12          4         10         71
9560033      11-DEC-11 29-NOV-12          0         11         61
9570904      29-MAR-12 31-OCT-12          0          7         74
9560033      11-DEC-11 29-NOV-12          0         11         61
9560033      11-DEC-11 17-DEC-12          1          0         31
0
Comment
Question by:diannagibbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 38767408
When you're calculating "DAYS", why are you using sysdate as your beginning date for the calculation ?

Try this maybe?

select ID, BIRTH, MEETING_DATE,
trunc(months_between(MEETING_DATE,BIRTH)/12) year,
    trunc(mod(months_between(MEETING_DATE,BIRTH),12)) month,
   trunc(MEETING_DATE-(add_months(BIRTH,trunc(months_between(MEETING_DATE,BIRTH)/12)*12+trunc(mod(months_between(MEETING_DATE,BIRTH),12))))) day
   from ica_data
/

I ran the query against your data and get this (which looks better):

        ID BIRTH     MEETING_D       YEAR      MONTH        DAY
---------- --------- --------- ---------- ---------- ----------
   8900007 01-JAN-08 29-NOV-12          4         10         28
   9560033 11-DEC-11 29-NOV-12          0         11         18
   9570904 29-MAR-12 31-OCT-12          0          7          2
   9560033 17-DEC-11 17-DEC-12          1          0          0

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38767447
you're using sysdate where you should be using meeting_date
here's a reorganized version that uses the same calculations but hopefully a little easier to read


select id, birth,meeting_date,
       year,month, meeting_date - add_months(birth,12*year+month) day
from (SELECT id,
       birth,
       meeting_date,
       TRUNC(MONTHS_BETWEEN(meeting_date, birth) / 12) year,
       TRUNC(MOD(MONTHS_BETWEEN(meeting_date, birth), 12)) month
  FROM ica_data)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38767459
oops, slow, same idea different arrangement
0
 

Author Comment

by:diannagibbs
ID: 38767556
Oversite on my part - forgot to change that to the meeting date instead of sysdate.  Thanks so much to you both.  Exactly what I was looking for.  Appreciate fast respone.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

762 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