DATEDIFF function in Oracle

[misaw]

for portability in SQL query ie a query written for one DB runs fine for other
i try to standardize our SQL queries as far as i can....
For finding the date difference we have:

DATEDIFF function in SQL Server
- operator in Oracle ...

i want to Sync this also how would i do that

i tried to create same name function in Oracle but having problem... of parameter type....what interval type should i take so that it works like SQL server DATEDIFF function.

[debasisdas]

If you want to write your own function well and good ,but simply if you wnat the different of 2 dates just use simple substration in oracle syntax.

ex

select date1-date2 from dual;

if u wnat to write the function both the parameters should be of DATE data type and the return type can be number.

[misaw]

If you want to write your own function well and good ,but simply if you wnat the different of 2 dates just use simple substration in oracle syntax.

ex

select date1-date2 from dual;

if u wnat to write the function both the parameters should be of DATE data type and the return type can be number.

let me more clearify my requirement....

What i actually want is to run the same query on Different DB, since this query is using Oracle - operator does'nt work as required in SQL Server.
select date1-date2 from dual;


What i want is if i write in SQL Server the following query
SELECT DATEDIFF(day, myDATE, GETDATE()) AS NumberOfDays
FROM myTable

This exactly without any change run fine in oracle ... this requires the DATEDIFF function in Oracle which is not there so i want create one for this ... but what about the first parameter of this function

please see detail of DATEDIFF at following link
http://msdn2.microsoft.com/en-us/library/ms189794.aspx

but i cannot under stand what type of this parameter is day dd d

thanks for your co-operation

[krispc]

Did this ever get resolved I am fighting the exact same issue. And since Oracle does not support the ODBC TIMESTAMPDIFF function I can't quite get a handle on how to handle both databases with the same sql.

[dthao]

You should be able to get the number of days by subtract date1 - date2.

Example

select name, date1-date2 days from customers

name days
bob 1
jane 5
rick 10

Hope this helps.