Openquery convert yyyymmdd to MM/DD/YYYY

Question

I have tried

FROM OPENQUERY(TESTsvr, 
Select to_date(CREATE_DT, "MM/DD/YYYY") as Testing
From Test.ViewTest

and

FROM OPENQUERY(TESTsvr, 
to_char(CREATE_DT, "mm/dd/yyyy hh24:mi:ss") as Testing
From Test.ViewTest

but when I execute it says invalid identifier.

Answer 1

Try this with quotes: If you are casting date into char:

  FROM OPENQUERY(TESTsvr, 
'select to_char(CREATE_DT, ''mm/dd/yyyy hh24:mi:ss'') as Testing From Test.ViewTest')

If you are casting char into date:

  FROM OPENQUERY(TESTsvr, 
'select to_date(CREATE_DT, ''mm/dd/yyyy'') as Testing From Test.ViewTest')

If you are casting NUMBER(8) into date (as yyyymmdd):

  FROM OPENQUERY(TESTsvr, 
'select to_date(CREATE_DT, ''yyyymmdd'') as Testing From Test.ViewTest')