sqloracle-databasedateto-charora-00936

Oracle SQL - ORA-00936 on DATE()

so for some reason oracle sql doesn't like this:

select count(DEPARTURE_DATE) as DEPARTURES_THIS_MONTH
 from DEPARTURE_DATES
 where to_char(DEPARTURE_DATES.DEPARTURE_DATE, 'MMYYYY') = to_char(date('2014-10-11 00:00:00'), 'MMYYYY')

Can anyone see what the problem with this is? I've spent 30 minutes looking at this and can't figure out what its on about...

ORA-00936: missing expression
Solution

  • date isn't a function in Oracle, so to_char(date('2014-10-11 00:00:00'), 'MMYYYY') means nothing.

    However, there is a to_date function which works like the inverse of to_char.

    Instead of date('2014-10-11 00:00:00'), something like this should work:

    TO_DATE('2014-10-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')