Solved

DATEDIFF function in Oracle?

Posted on 2011-02-23
18
1,623 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

773 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