0

The following Microsoft SQL query compares two date fields of a table and returns those records for which the difference in minutes is greater than 5.

SELECT t.Id, t.date1, t.date2, DATEDIFF(MINUTE, t.date1 , t.date2) AS Mtime FROM table1 t WHERE DATEDIFF(MINUTE,t.date1, t.date2) > 5

I have no idea how to write this with ORACLE. I've searched for solution and the closest I came to was : 

SELECT t.date1, t.date2, (t.date1 - t.date2) * 1440 AS Mtime FROM table1 t WHERE (t.date1 -t.date2) * 1440 > 5

which gives me the error inconsistent datatypes: expected INTERVAL DAY TO SECOND got NUMBER

Does anyone know how to write this query with ORACLE ?

Improve this question
3
  • if you use the SQL Developer migration assistant, it makes a UTILS package available - one of the functions it comes with is an Oracle equivalent for DATEDIFF() Commented Apr 24, 2018 at 12:21
  • what are the data types of your table? That sql you posted should have worked for date fields Commented Apr 24, 2018 at 12:48
  • 1
    the type is TimeStamp(4) Commented Apr 24, 2018 at 12:52

2 Answers 2

Reset to default
3

Don't use the difference. Just add the interval:

WHERE t.DeliveryDate >= t.Deadline + interval '5' minute

Or:

WHERE t.DeliveryDate >= t.Deadline + 5 / (24 * 60)

The equivalent in SQL Server is:

WHERE t.DeliveryDate >= DATEADD(minute, 5, t.Deadline)

This is a good habit. If one of the values is a constant, then the use of a function (- or datediff()) prevents the use of an index.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, that is working. WHERE t.DeliveryDate >= t.Deadline + 5 / (25 * 60)
Why 25 * 60? Shouldn't it be 24 * 60 or 1440?
0

This should work - 

SELECT t.Id, t.date1, t.date2, (CAST(t.date1 AS DATE)-CAST(t.date2 AS DATE)) * 1440 AS Mtime FROM table1 t where (CAST(t.date1 AS DATE)-CAST(t.date2 AS DATE)) * 1440 > 5
Improve this answer

3 Comments

(t.date1 -t.date2) > 5 how it will compare with 5 minutes and your answer is not even correct is about datetime not date
It is actually the TIMESTAMP(4) type.
Thanks @JayShankarGupta that was my bad.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.