"ORA-00936: missing expression" on DATE

Question

at work we're undergoing a migration from DB2 for iSeries SQL to Oracle SQL and are experiencing some pains. I'd still consider myself a beginner with SQL and can't figure out what Oracle SQL doesn't like about this clause.

  CASE 
        WHEN YYCOL IS NULL THEN NULL 
        ELSE max ( DATE ( MMCOL || '/' || DDCOL || '/' || YYCOL ) ) 
  END AS COLUMN007 

When querying with the above clause:

ORA-00936: missing expression
            ELSE max ( DATE ( MMCOL || '/' || DDCOL || '/' || YYCOL ) ) 
                       ^

If possible, is there some easy to digest documentation for moving from DB2 for iSeries SQL to Oracle SQL? Any help is appreciated!

Answer 1

You have to use -

CASE 
        WHEN YYCOL IS NULL THEN NULL 
        ELSE max ( TO_DATE ( MMCOL || '/' || DDCOL || '/' || YYCOL, 'MM/DD/YY' ) ) 
  END AS COLUMN007 

Answer 2

"so it only shows the date stamp" The to_date converts the provided string to oracle internal, binary DATE datatype, which also always includes the time down to the second. But to "show" a date, that DATE type has to be converted back to a string of characters. This is normally done by use of TO_CHAR, providing a DATE and a 'format mask'. Since you did not provide that, oracle was forced to do it implicitly, and use the effective value of NLS_DATE_FORMAT. If want it to only "show" the date, then

to_char(to_date(MMCOL||'/'||DDCOL||'/'||yycol,'mm/dd/yy'),'mm/dd/yyyy')

I encourage you to go to the official oracle docs and look up the to_date and to_char function. See also an article I wrote on it, at https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

As an aside, please quit using 2-digit years. 20 years ago I and thousands of guys like me were busting our butts to prevent a meltdown on 1-Jan-2000. And just last week I read an article that systems that had kicked the can down the road with a 'pivot date' were now coming to grief as of 1-Jan-2020. Please quit repeating the same mistakes we were supposed to have corrected 20 years ago.