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.

vercelli · Accepted Answer · 2019-03-07T16:24:50.657

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

edited Mar 07 '19 at 16:24

answered Mar 07 '19 at 16:12

vercelli

@jrussin what datatype is Test.ViewTest.CREATE_DT? – vercelli Mar 07 '19 at 16:16
@jrussin ok it's a number but it's storing a date? What format? – vercelli Mar 07 '19 at 16:21
It is storing as YYYYMMDD – jrussin Mar 07 '19 at 16:23
1

Boom! you are the best! Idk why I made this so difficult haha. – jrussin Mar 07 '19 at 16:28

1 Answers1