What date format does Oracle/Toad expect?

sql oracle date toad
B. Clay Shannon picture B. Clay Shannon · Apr 18, 2012 · Viewed 41.1k times · Source

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?

Answer

cagcowboy picture cagcowboy · Apr 18, 2012

Safest to use TO_DATE:

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

Related questions

Updating a date in Oracle SQL table

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query: Select ASOFDATE from PASOFDATE; I get 4/16/2012 I tried running this query UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; but it …

Read More
Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More
Oracle SQL - DATE greater than statement

As the title says, I want to find a way to check which of my data sets are past 6 months from SYSDATE via query. SELECT * FROM OrderArchive WHERE OrderDate <= '31 Dec 2014'; I've tried the following but it returns …

Read More