Solved

DATEDIFF function in Oracle?

Posted on 2011-02-23
18
1,602 Views
Last Modified: 2012-05-11
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
Comment
Question by:blossompark
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 350 total points
ID: 34961377
In oracle, date math is in days


sysdate - SCHEDULED_START_DATE  <= 90
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 100 total points
ID: 34961391
where trunc(sysdate) - trunc(SCHEDULED_START_DATE) <= 90
0
 
LVL 18

Assisted Solution

by:sventhan
sventhan earned 100 total points
ID: 34961400
Sorry! I'm little late.
0
 

Assisted Solution

by:blossompark
blossompark earned 0 total points
ID: 34962061
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
 

Author Comment

by:blossompark
ID: 34962088
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34962198
is scheduled_start_date  a "DATE" type?

you probably don't need the TRUNC calls
0
 

Author Comment

by:blossompark
ID: 34962496
removed TRUNc...but same error
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34962512
is scheduled_start_date  a "DATE" type?

0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 50 total points
ID: 34962987
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34963034
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
 

Author Comment

by:blossompark
ID: 34969781
Hi all...
sorry about delay in responding...
Scheduled_start_date returns the following example
2011-01-27 06:00:00.000
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34969819
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34969832
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
 

Author Comment

by:blossompark
ID: 34970919
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 350 total points
ID: 34970968
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
 

Author Comment

by:blossompark
ID: 34972157
Hi sdstuber,,,let me try and find iout the specific data type...it may take a while as it is a linked server
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35097536
if you don't accept your own post, the question will close immediately
0
 

Author Closing Comment

by:blossompark
ID: 35135856
sorry for the late response to this...thanks for all your help, it is greatly appreciated
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now