Oracle date difference SQL query

Question

Possible Duplicate:
Calculate difference between 2 date / times in Oracle SQL

I have a table where PurchasedDate is maintained. I have to get the date from the system and calculate the date difference in days. datediff() is not giving tHe result as I am working in Oracle.

Sample table:

ProductID  Date
P101       31-DEC-2012
P102       29-DEC-2011

If P102 is returned today, the difference should be 10.

Here is a detailed description

BillID     ProductID   Date
101          P101        31-DEC-2012
101          P102        31-DEC-2012
102          P405        29-JUN-2012
102          P210        29-JUN-2012
105          P205         2-MAY-2012

If a customer comes to retun a product P101 with BillNo 101, then the system should calcualte the difference between the return date(which is the system date) and the purchased date. The answer for P101 should be 10 if its returned on Jan 8 2012

When P102 is returned on Jan 8, the difference is 10 since the purchased date is 29 Dec — Ussamah Habib, Jan 09 '13 at 08:33

score 12 · Accepted Answer · answered Jan 08 '13 at 15:09

12

try

SELECT trunc(sysdate) - PurchasedDate 
  FROM table_name;

answered Jan 08 '13 at 15:09

A.B.Cade

16,735
1
37
53

score 3 · Answer 2 · answered Jan 08 '13 at 15:08

3

in Oracle you can just subtract the dates. this will give you an answer in days.

eg assuming you meant 29-dec-2012 and not 29-dec-2011!

select trunc(sysdate) - your_date from your_table;


SQL> select trunc(sysdate) - to_date('29-dec-2012', 'dd-mon-yyyy') from dual;

TRUNC(SYSDATE)-TO_DATE('29-DEC
------------------------------
                            10

answered Jan 08 '13 at 15:08

DazzaL

21,638
3
49
57

The table is a segment of sales table. BillId and ProductID will form primary key. If a customer wants to return a product, the purchased date is taken from the table and date is taken from the system and the difference is calculated. – Ussamah Habib Jan 09 '13 at 08:38

score 0 · Answer 3 · answered Jan 08 '13 at 15:39

0

you can find good information about calculating date differences in oracle from below link

How To Calculate Difference Between Dates in Oracle SQL

answered Jan 08 '13 at 15:39

Sri

283
2
6
15

Thanks, Here @A.B Cade madi it all he more simple – Ussamah Habib Jan 09 '13 at 09:02
Sadly, the link is broken – StewS2 Oct 08 '20 at 21:15

Oracle date difference SQL query

3 Answers3