Converting query from Microsoft SQL to Oracle

Question

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 ?

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() — thatjeffsmith
– thatjeffsmith, 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 — Peter M
– Peter M, Commented Apr 24, 2018 at 12:48

Gordon Linoff · Accepted Answer · 2018-04-25 03:39:59Z

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.

edited Apr 25, 2018 at 3:39

answered Apr 24, 2018 at 12:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Sam Over a year ago

Thanks, that is working. WHERE t.DeliveryDate >= t.Deadline + 5 / (25 * 60)

David Faber Over a year ago

Why 25 * 60? Shouldn't it be 24 * 60 or 1440?

Be1ng_Kr1Sh · Accepted Answer · 2018-04-24 12:57:23Z

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

edited Apr 24, 2018 at 12:57

answered Apr 24, 2018 at 12:38

Be1ng_Kr1Sh

4035 silver badges16 bronze badges

3 Comments

Jay Shankar Gupta Over a year ago

(t.date1 -t.date2) > 5 how it will compare with 5 minutes and your answer is not even correct is about datetime not date

Sam Over a year ago

It is actually the TIMESTAMP(4) type.

Be1ng_Kr1Sh Over a year ago

Thanks @JayShankarGupta that was my bad.

Collectives™ on Stack Overflow

Converting query from Microsoft SQL to Oracle

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related