Solved

DATEDIFF function in Oracle?

Posted on 2011-02-23
18
1,621 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Sums/Grouping 7 51
grouping logic 6 49
mysql joining from the same table 6 37
Common Records between Sub Queries 4 14
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

861 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

22 Experts available now in Live!

Get 1:1 Help Now