Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1887
  • Last Modified:

DATEDIFF function in Oracle?

Hi,
      I am connecting to an Oracle database via a linked server in  sql server 2005.
There is a column in the Oracle database SCHEDULED_START_DATE that I want to use in the WHERE clause,
That is, return rows where the SCHEDULED_START_DATE is less than or equal to todays date.
In SQL Server I would use the following:
where datediff("dd",SCHEDULED_START_DATE ,  getdate()) <= 90

Is there an equivalent in Oracle?
0
blossompark
Asked:
blossompark
  • 8
  • 7
  • 2
  • +1
11 Solutions
 
sdstuberCommented:
In oracle, date math is in days


sysdate - SCHEDULED_START_DATE  <= 90
0
 
sventhanCommented:
where trunc(sysdate) - trunc(SCHEDULED_START_DATE) <= 90
0
 
sventhanCommented:
Sorry! I'm little late.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
blossomparkAuthor Commented:
Hi  sdstuber and sventhan, thanks for your replies!
tried both ...got the following error returned
[OLE/DB provider returned message: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
0
 
blossomparkAuthor Commented:
the query i am using is;
SELECT  * FROM OPENQUERY(REMEDY_STANDBY,'SELECT
 ADMIN.fn_adjusted_date(SCHEDULED_START_DATE) as Scheduled_Start_Date,
ADMIN.fn_adjusted_date(SCHEDULED_END_DATE) as Scheduled_End_Date,
INFRASTRUCTURE_CHANGE_ID,
DESCRIPTION as Summary,
dbms_lob.substr(chg_infrastructure_change.detailed_description,2000,1)as Notes,
SUPPORT_ORGANIZATION2
FROM ADMIN.CHG_INFRASTRUCTURE_CHANGE
where trunc(sysdate) - trunc(SCHEDULED_START_DATE) <= 90
')
0
 
sdstuberCommented:
is scheduled_start_date  a "DATE" type?

you probably don't need the TRUNC calls
0
 
blossomparkAuthor Commented:
removed TRUNc...but same error
0
 
sdstuberCommented:
is scheduled_start_date  a "DATE" type?

0
 
Aaron ShiloChief Database ArchitectCommented:
hi

option 1:

sysdate-to_date(scheduled_start_date  ,'yyyymmdd')<=90

option 2:

create or replace function diffdate (date1 in date, date2 in date)
   return number is
   begin
     return date2-date1;
   end;
   /
 
select diffdate (sysdate,to_date(scheduled_start_date  ,'yyyymmdd')) from dual;
0
 
sdstuberCommented:
to_date(scheduled_start_date  ,'yyyymmdd')  - this assumes the column is a string in yyyymmdd format.

based on the error, it's a number.

the diffdate function is the same thing as posted previously  except requiring a sql-pl/sql context switch in addition to the function call itself  So more expensive but no added functionality, it also assumes 2 dates, but again, as the error indicates it's not really a DATE.

waiting on asker to provided data type and example
0
 
blossomparkAuthor Commented:
Hi all...
sorry about delay in responding...
Scheduled_start_date returns the following example
2011-01-27 06:00:00.000
0
 
sdstuberCommented:
I'm sorry, that doesn't help

is it a TIMESTAMP that you are formatting to look like that (either intentionally or implicitly)?
or is it a string?


if it's a string (varchar2,char) then you must convert it to a date with TO_DATE but strip off the fractional piece since DATE types don't support resolution  less than a second
,  you can't do date math on strings.

If it's a timestamp then the problem is the math will generate an INTERVAL type not a number
use this


sysdate - (SCHEDULED_START_DATE+0)  <= 90


the +0 will do an implicit conversion of the timestamp to a date type
0
 
sdstuberCommented:
or leave the timestamps intact and use interval math, which you might prefer since it looks a little more like DATEDIFF syntax

try this...


EXTRACT(DAY from (systimestamp - scheduled_start_date)) <= 90
0
 
blossomparkAuthor Commented:
hi sdstuber,
used EXTRACT(DAY from (systimestamp - scheduled_start_date)) <= 90

returned the following message
[OLE/DB provider returned message: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e07].
Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
0
 
sdstuberCommented:
All of my suggestions above have been based on GUESSES as to what your column is.

Please tell me

What is SCHEDULED_START_DATE?

Is it a date?
Is it a string?
Is it a timestamp?
Is it something else?

based on the errors you have given thus far I think it's a string, in which case, as noted above, you should use the date math (not timestamp/interval math)  with to_date using the appropriate format

0
 
blossomparkAuthor Commented:
Hi sdstuber,,,let me try and find iout the specific data type...it may take a while as it is a linked server
0
 
sdstuberCommented:
if you don't accept your own post, the question will close immediately
0
 
blossomparkAuthor Commented:
sorry for the late response to this...thanks for all your help, it is greatly appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now