CODEWITHSUNDEEP
sqloracle-databasedateto-charora-00936
Bart
Bart

Reputation: 55

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

Upvotes: 2

Views: 5465

Answers (2)

toddlermenot
toddlermenot

Reputation: 1618

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.

Upvotes: 1

Mr. Llama
Mr. Llama

Reputation: 20889

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

Upvotes: 5

Related Questions