1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Generic DateDiff query for Oracle and SQL server

Discussion in 'SQL PL/SQL' started by SPIND, May 15, 2009.

  1. SPIND

    SPIND Guest

    Hi,

    Have a requirement to use datediff function for calculating the difference in days. My generic query should be able to execute both on Oracle and SQL Server. DateDiff is keyword for SQL Server. So how do I write the generic query? What is the best possible option available under such circumstances - where we need to write generic queries?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    What is actually the need to prepare generic SQL oto work in both Oracle and SQL Server ? Is it just out of curiosity ?

    Anyway .. You can also create a UDF (User defined funcyion) DateDiff in Oracle to return the date difference.
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    well oracle has no keyword like datediff so obviously a generic query like the below query won't work in oracle.
    Code (Text):
    SELECT DATEDIFF(dd, date_var , GETDATE())
    For finding the date difference in oracle you need to simply subtract the two date variables like the below query
    Code (Text):
    select date_var-sysdate from dual
    Does subtracting date variables work in SQL server? if yes you should use this method to write a query which would run on both.

    P.S. I am very curious as to for what application would you want to write a query that runs on both oracle and sql server?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The behaviour of DATEDIFF is slightly different as per this link DATEDIFF ( I am not good @ SQL Server ; even I ma very bad :( )

    And if you want to get the same behavior you have no other choice than creating a user defined function.

    Again .. What is the purpose of this exercise ?