Solved

DATEDIFF function in Oracle?

Posted on 2011-02-23
18
1,672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 74

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
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 

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 74

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 74

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
 
LVL 74

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 74

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 74

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 74

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 74

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

623 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