Date format in where clause-Oracle

Question

I have one table where date column is having data in below format: "7/25/2014 12:14:27 AM'. I need to fetch this date by putting in the where clause. can anyone suggest how can i do this?

Answer 1

Dates (stored in tables) are represented by 7 bytes - they do not have any format associated with them. If they are formatted as a string then that is the client program which you are using to access the database applying its own formatting to the date (which you can usually set via the preferences in that program).

If the "date" is stored with a format then you are not storing it as a date but storing it as a string (i.e. VARCHAR2) format.

Query - if the date is stored as a date:

SELECT *
FROM   table_name
WHERE  date_column = TO_DATE( '7/25/2014 12:14:27 AM', 'MM/DD/YYYY HH12:MI:SS AM' )

or, using ANSI/ISO literals:

SELECT *
FROM   table_name
WHERE  date_column = TIMESTAMP '2014-07-25 00:14:27'

or, if you want values which are just for a given day then:

SELECT *
FROM   table_name
WHERE  date_column >= DATE '2016-05-12'
AND    date_column <  DATE '2016-05-13'

(This will allow you to use any indexes on the date_column column.)

or, only passing a single day value (represented by the bind variable :date_value):

SELECT *
FROM   table_name
WHERE  date_column >= :date_value
AND    date_column <  :date_value + INTERVAL '1' DAY

Query - if the "date" is stored as a string:

SELECT *
FROM   table_name
WHERE  TO_DATE( date_as_a_string_column, 'MM/DD/YYYY HH12:MI:SS AM' )
         = TIMESTAMP '2014-07-25 00:14:27'

or, just simply:

SELECT *
FROM   table_name
WHERE  date_as_a_string_column = '7/25/2014 12:14:27 AM'

Answer 2

Most likely the date format you see is the format your SQL tool (SQLplus, SQL Developer, Toad etc.) uses. Proper date columns don't have an associated date format.

To write a reliable query for selecting by date, explicitly specify the date format in your query (otherwise Oracle we use the date format from your current session):

SELECT * FROM T1
WHERE DATE_COL > TO_DATE('7/25/2014 12:14:27 AM', 'MM/DD/YYYY HH:MI:SS AM');

Any other date format will also work:

SELECT * FROM T1
WHERE DATE_COL > TO_DATE('2014-07-25 12:14:27', 'YYYY-MM-DD HH24:MI:SS');