CODEWITHSUNDEEP
sqloracle-databasedate
user3750517
user3750517

Reputation: 1

Oracle to SQL Server Date Conversion: Error invalid relational operator

Need to convert date format from (source oracle) yyyy/mm/dd to (target - SQL Server) mm/dd/yyyy.

Current Query:

SELECT* FROM WVT.WVCAS 
WHERE to_date(dttmcutpull, 'yyyy/mm/dd hh24:mi:ss', 'mm/dd/yyyy hh24:mi:ss')

Getting error:

ORA-00920: invalid relational operator

Please help.

Upvotes: 0

Views: 1444

Answers (2)

ngrashia
ngrashia

Reputation: 9904

SELECT w.*,
       TO_CHAR(to_date(dttmcutpull, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NewDate
FROM WVT.WVCAS w;

What I am doing is,

  • dttmcutpull is a STRING (VARCHAR) with format yyyy/mm/dd hh24:mi:ss
  • Initially converting the string to a DATE using TO_DATE
  • Re-converting the DATE to a string of mm/dd/yyyy hh24:mi:ss using TO_CHAR function

Used Syntax is similar to

SELECT
TO_CHAR 
( 
    TO_DATE (STRING_FIELD_VALUE, EXISTING_FORMAT_OF_STRING_FIELD_VALUE),
    EXPECTED_FORMAT_OF_NEW_VALUE
) AS NEW_STRING_FIELD_VALUE;

EDIT: Your Query should be:

  1. If dttmcutpull, DTTMPULLll,DTTMRUNll etc are DATE datatypes then

    SELECT w.*, to_char(dttmcutpull, 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE1, to_char(DTTMPULLll, 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE2, to_char(DTTMRUNll, 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE3, to_char(SYSLOCKDATEll, 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE4, to_char(SYSMODATEll, 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE5, to_char(SYSCREATEDATE 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE6 FROM WVT.WVCAS w;

  2. If dttmcutpull, DTTMPULLll,DTTMRUNll etc are VARCHAR(STRING/CHARACTER) datatypes then

    SELECT w.*, TO_CHAR(to_date(dttmcutpull, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE1, TO_CHAR(to_date(DTTMPULLll, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE2, TO_CHAR(to_date(DTTMRUNll, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE3, TO_CHAR(to_date(SYSLOCKDATEll, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE4, TO_CHAR(to_date(SYSMODATEll, 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE5, TO_CHAR(to_date(SYSCREATEDATE , 'yyyy/mm/dd hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss') as NEW_DATE6 FROM WVT.WVCAS w;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269743

"Invalid relational operator" usually means that you have a where clause without a comparison. In your case, the where clause has the conversion to_date(), but no comparison. Perhaps you mean something like:

SELECT *
FROM WVT.WVCAS 
WHERE dttmcutpull > sysdate - 1;

In other words, merely converting the data is not sufficient, you have to compare it to something.

If you just want to do the conversion, then put that in the select:

SELECT w.*,
       to_char(dttmcutpull, 'mm/dd/yyyy hh24:mi:ss') as NewDate
FROM WVT.WVCAS w;

EDIT:

You have to convert each column independently, not all at once. to_char() takes two arguments, a date and a format:

SELECT w.*,
       to_char(dttmcutpull, 'mm/dd/yyyy hh24:mi:ss') as date1,
       to_char(DTTMPULLll, 'mm/dd/yyyy hh24:mi:ss') as date2,
       to_char(DTTMRUNll, 'mm/dd/yyyy hh24:mi:ss') as date3,
       to_char(SYSLOCKDATEll, 'mm/dd/yyyy hh24:mi:ss') as date4,
       to_char(SYSMODATEll, 'mm/dd/yyyy hh24:mi:ss') as date5,
       to_char(SYSCREATEDATE 'mm/dd/yyyy hh24:mi:ss') as NewDate
FROM WVT.WVCAS w;

Upvotes: 1

Related Questions