Question User Question User - 3 months ago 24
SQL Question

how can i use date diff in oracle. I need no of days how can i achieve this

I have table like this. I need to use date diff im new to oracle. How can i find no of days in oracle.

Sql

SELECT * FROM TICKETS WHERE SUBMITTED_ON-1


Tickets Table

TICKET_ID |SUBJECT |CHANGE_TYPE |PRIORITY |SUBMITTED_ON
----------|----------------------------|------------|---------|--------------------
343 |Test CR child 123 |4 |4 |2015-06-11 08:24:17
345 |New test today |2 |3 |2015-06-11 08:45:16
352 |CR for child ticket purpose |2 |4 |2015-06-15 04:53:26


How to find the date differnce btween SUBMITTED_ON - CURRENT DATE with no of days ?

How can i achieve this ?

Answer

You would subtract them and use trunc() to remove the time component. Depending on what you want, this is either:

SELECT t.*,
       TRUNC(sysdate) - TRUNC(submitted_on)
FROM TICKETS t;

or:

SELECT t.*,
       TRUNC(sysdate - submitted_on)
FROM TICKETS t;

Note: This appears to be the answer to your question. But if you want to test the values in the WHERE clause, then Matthew's answer is the best approach.

Comments