SQL Datetime: add time on Oracle and SQL Server

Question

Having database servers on both SQL Server and Oracle Server,

I couldn't find any homogeneous answer for adding time to a date.

What I would do on SQL Server:

select dateadd(minute, 1, CreationDate) from comments

and on Oracle Server (even though this isn't a pretty solution):

select CreationDate + (1/1440) from comments

1440 being the minutes in a day.

Is there any solution that would work on both servers?

Does SQL Server support ANSI SQL? creationdate + interval '1' minute (which works in Oracle) — user330315
– user330315, Commented Jul 15, 2015 at 14:38
Unfortunately, date/time handling is one area where SQL Server is still vastly different from the standard. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 15, 2015 at 14:38
@a_horse_with_no_name that doesn't seem to work under SQL Server. — Majid
– Majid, Commented Jul 15, 2015 at 14:48
Why is it important to find a solution that works on both servers? Maybe there is another solution to the underlying problem. — Tab Alleman
– Tab Alleman, Commented Jul 15, 2015 at 15:08
There could be. I needed a solution that works on both servers, so I can (when setting the queries in my code) make abstraction of the SQL engine and not worry if it would work on one or another. — Majid
– Majid, Commented Jul 15, 2015 at 21:22

score 1 · Accepted Answer · 2015-07-15 15:59:09Z

1

I don't know if this works on Oracle but this works on SQL Server (Tested on 2014 Ex. Edition):

select CreationDate + (1./1440) from comments

The only thing that's different to your oracle solution is the dot behind 1 - just to tell sql server that this is not an integer column but a float.

1 Answer 1