What date format does Oracle/Toad expect?

Question

In the Toad editor, I've got this SQL:

SELECT INTERLOPERABCID,AVAILABLEDATE,
AVAILABLEQHR,CARPHONEID,
TRUNC((AVAILABLEQHR-1)/12) "COL",
MOD(AVAILABLEQHR-1,12) "ROW"
FROM ABC.INTERLOPERAVAILABILITY
WHERE INTERLOPERABCID>42
AND AVAILABLEDATE='09/Apr/2012'

...but it returns no rows, even though I see many records in the table whose AVAILABLEDATE value = '4/9/2012'

The date format ('09/Apr/2012'; I also tried '04/Sep/2012') looks odd, but at least I get no compilation/execution error with it. When I tried "4/9/2012" and "04/09/2012" it said "ORA-01853: not a valid month"

How must I enter the date to tell it I want to see dates of April 9th, 2012?

Fanlight

The date format depend on the NLS_DATE_FORMAT setting, that depends on NLS_TERRITORY. Both can be session specific. Never rely in a 'default' dateformat, use a proper date mask in to_date functions. – Abagail

Answer 1

Safest to use TO_DATE:

AND AVAILABLEDATE = TO_DATE('09/Apr/2012', 'DD/Mon/YYYY')

Horticulture

Answer 2

I like to use the date literal:

SELECT INTERLOPERABCID,AVAILABLEDATE,
AVAILABLEQHR,CARPHONEID,
TRUNC((AVAILABLEQHR-1)/12) "COL",
MOD(AVAILABLEQHR-1,12) "ROW"
FROM ABC.INTERLOPERAVAILABILITY
WHERE INTERLOPERABCID>42
AND AVAILABLEDATE = DATE '2012-04-09'

Are you sure there is no time part in your dates?

Ret

The Data type is "Date" and there is no time element that displays in the DB for that column. – Fanlight

Both of the answers work, so which one to choose as "the" answer was a tossup. – Fanlight

This is quite unusual that you have to prefix the date with the DATE type specifier. I would have never expected it as I assumed oracle would be clever enough to cast it or whatever like the sql server does. – Foah