dateAdd function in Query Analyser
-
I would like to run the following set of SQL code which is pretty self explanatory:
SELECT DISTINCT * FROM OPENQUERY("aserverName", 'select
DATEADD(DAY, 21,DATEAPVAL) AS D
FROM
DCAPPL
')
The code above refers to a linked server, whcih works fine. I need to add 21 days to a date/Time colum.
I receive the error:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "DATEADD": invalid identifier
]
The date/Time field is pulling data in the format [yyyy/mm/dd 00:00:00.000] -
* would this affect the dateAdd function???
* how can I convert the above field to just display "yyyy/mm/dd"?
-
To solve this kind of problem, I would recommend the following approach:
1. Simplify it until you find something which works
2. Build it up bit by bit until it breaks
3. Figure a way around that specific issue (e.g. by posting a question)
So...
Maybe try running each of the following (in turn) and let us know what works / what doesn't.
1. SELECT * FROM OPENQUERY("aserverName", 'select DATEAPVAL FROM DCAPPL')
2. SELECT DATEAPVAL FROM OPENQUERY("aserverName", 'select DATEAPVAL FROM DCAPPL')
3. SELECT DISTINCT DATEAPVAL FROM OPENQUERY("aserverName", 'select DATEAPVAL FROM DCAPPL')
4. SELECT DISTINCT DATEADD(DAY, 21, DATEAPVAL) FROM OPENQUERY("aserverName", 'select DATEAPVAL FROM DCAPPL')
Hope that helps
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
OR Find which is the equivalent of DAYADD in Oracle
* Noel -
For the second question, the CONVERT function can format dates. See BOL for all the different options.
To format a date as yyyy/mm/dd, use CONVERT(char(10), DateToFormat, 111)
SELECT
CONVERT(char(10), getdate(), 111)
-Eddie
Eddie Wuerch
MCM: SQL -
I don't know, you try to teach a man to fish, and someone hands him their catch...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
iTS SEEMS GIVING A MAN THE CATH HELPS A GREAT DEAL.
I have located the equivalent method for Oracle its:
+(DATEAPVAL+91) AS EXPIRYDATE
The above adds 91 days to the date value.
Brilliant.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply