Oracle SQL - ORA-00936 on DATE()

Question

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

Answer 1

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

Answer 2

There is no date function in Oracle although there is a to_date()

You probably want this:

select count(DEPARTURE_DATE) as DEPARTURES_THIS_MONTH
 from DEPARTURE_DATES
 where to_char(DEPARTURE_DATES.DEPARTURE_DATE, 'MMDDYYYY') = '11102014';

There is no point in converting a String to Date and then back to String. 'DD' part in the format mask was missed, you probably want that too.