DATEDIFF function in Oracle?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

removed TRUNc...but same error

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Aaron Shilo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi all...
sorry about delay in responding...
Scheduled_start_date returns the following example
2011-01-27 06:00:00.000

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi sdstuber,,,let me try and find iout the specific data type...it may take a while as it is a linked server

if you don't accept your own post, the question will close immediately