Oracle: How to filter by date and time in a where clause

Question

How can I do this:

select *
from tableName
where SESSION_START_DATE_TIME > To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )

SESSION_START_DATE_TIME is in the format '12/01/2012 13:16:32.000'

I tried where To_Date (SESSION_START_DATE_TIME, 'DD-MON-YYYY hh24:mi') > To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )

but no matter what I try I get the error: SQL command not properly formed

Answer 1

In the example that you have provided there is nothing that would throw a SQL command not properly formed error. How are you executing this query? What are you not showing us?

This example script works fine:

create table tableName
(session_start_date_time DATE);

insert into tableName (session_start_date_time) 
values (sysdate+1);

select * from tableName
where session_start_date_time > to_date('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi');

As does this example:

create table tableName2
(session_start_date_time TIMESTAMP);

insert into tableName2 (session_start_date_time) 
values (to_timestamp('01/12/2012 16:01:02.345678','mm/dd/yyyy hh24:mi:ss.ff'));

select * from tableName2
where session_start_date_time > to_date('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi');

select * from tableName2
where session_start_date_time > to_timestamp('01/12/2012 14:01:02.345678','mm/dd/yyyy hh24:mi:ss.ff');

So there must be something else that is wrong.

Answer 2

If SESSION_START_DATE_TIME is of type TIMESTAMP you may want to try using the SQL function TO_TIMESTAMP. Here is an example:

     SQL> CREATE TABLE t (ts TIMESTAMP);

     Table created.

     SQL> INSERT INTO t
       2       VALUES (
       3                 TO_TIMESTAMP (
       4                    '1/12/2012 5:03:27.221008 PM'
       5                   ,'mm/dd/yyyy HH:MI:SS.FF AM'
       6                 )
       7              );

     1 row created.

     SQL> SELECT *
       2    FROM t
       3   WHERE ts =
       4            TO_TIMESTAMP (
       5               '1/12/2012 5:03:27.221008 PM'
       6              ,'mm/dd/yyyy HH:MI:SS.FF AM'
       7            );
     TS
     -------------------------------------------------
     12-JAN-12 05.03.27.221008 PM

Answer 3

Put it this way

where ("R"."TIME_STAMP">=TO_DATE ('03-02-2013 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
   AND "R"."TIME_STAMP"<=TO_DATE ('09-02-2013 23:59:59', 'DD-MM-YYYY HH24:MI:SS')) 

Where R is table name.
TIME_STAMP is FieldName in Table R.

Answer 4

Try:

To_Date (SESSION_START_DATE_TIME, 'MM/DD/YYYY hh24:mi') > 
To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )