Select OpenQuery with date comversion.

[joejack0330]

Hi, trying to run an select with openquery in sql 2005 to select data from a linked oracle database. It works fine but trying to use convert on a date field and says missing expression. Is it possibly because of commas within the convert expression not correct?

SELECT *
FROM OPENQUERY(APP4, 'SELECT file#, file# as clock#, lastname, firstname, socialsecurity#, status, 999999, ratetype, 888888, companycode, homedepartment, homejob, ratetype as ratecode, CONVERT(CHAR(10),getdate(),101) as hiredate, rehiredate, terminationdate, birthdate from reports.v_emp_all')

Thanks,
Joe

 

[joejack0330]

Mistake: Sorry, the getdate() was just for testing, actually used valid field called hiredate.

 

[joejack0330]

Fixed: I needed to use Oracle conversion statement.

SELECT *
FROM OPENQUERY(APP4, 'SELECT file#, file# as clock#, lastname, firstname, socialsecurity#, status, 999999, ratetype, 888888, companycode, homedepartment, homejob, ratetype as ratecode, TO_CHAR(hiredate, ''MM/DD/YYY'') as DateHired, rehiredate, terminationdate, birthdate from reports.v_emp_all')

 

[SQLSister]

HAve you tried the Oracle equivalent of convert?

"NOTHING is more important in a database than integrity." ESquared