469,315 Members | 1,795 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

DATEDIFF function in Oracle

17
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.
Aug 13 '07 #1
4 112410
debasisdas
8,127 Expert 4TB
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.
Aug 13 '07 #2
misaw
17
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
Aug 15 '07 #3
krispc
1
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.
May 14 '09 #4
dthao
1
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.
Jun 30 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by mcbill20 | last post: by
2 posts views Thread by Derrick | last post: by
7 posts views Thread by JenM | last post: by
14 posts views Thread by cmdolcet69 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.