need syntax correction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need syntax correction

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2009-04-02 : 17:01:58

hi guys,
i am using a linked server to oracle .the data pulled fromoarcle is in epoch format.. i put this syntax and i am having syntax error :
DATEADD(ss, CAST(CREATE_DATE AS int), '01/01/1970')AS date.

is it correct ?
can we convert the date within the openquery..?

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 17:38:09

DATEADD(s, CAST(CREATE_DATE AS int), '01/01/1970') AS [date]

Since "date" is a reserved word you need to include the brackets around it, or you can change the name.

korssane
Posting Yak Master

104 Posts

Posted - 2009-04-02 : 23:31:21

HI ,
thanks for the reply but i have changed the name and still getting the same problem .
DATEADD(ss, CAST(CREATE_DATE AS int), '01/01/1970')AS CREATEDATE,
Msg 7357, Level 16, State 2, Line 34
Cannot process the object "SELECT

here is my code :

SELECT * FROM OPENQUERY(LINKED_ORA,
'SELECT
TROUBLEID,
DATEADD(ss,CAST(CREATE_DATE AS int), ''01/01/1970'')AS CREATEDATE
FROM ORACLETABLE
WHERE CREATE_DATE between ''1230768001'' AND ''1238677385''');
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-02 : 23:53:15

It appears the syntax error is in the Oracle query. Please check the syntax for the Oracle DateAdd() function, I don't think it's correct, I think you need quotes in there. Also, leave a space between the closing parenthesis and the word "AS".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources