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  gt  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    gt  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

User · Answer

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.

User · Answer

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 gt  CREATE TABLE t  ts TIMESTAMP         Table created        SQL gt  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 gt  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

User · Answer

Put it this way  where   R   TIME STAMP  gt  TO DATE   03-02-2013 00 00 00    DD-MM-YYYY HH24 MI SS      AND  R   TIME STAMP  lt  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

User · Answer

Try   To Date  SESSION START DATE TIME   MM DD YYYY hh24 mi    gt   To Date   12-Jan-2012 16 00    DD-MON-YYYY hh24 mi

User · Answer

Obviously  12 01 2012 13 16 32 000  doesn t match  DD-MON-YYYY hh24 mi  format   Update   You need  MM DD YYYY hh24 mi ss ff  format and to use TO TIMESTAMP instead of TO DATE cause dates don t hold millis in oracle

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

Examples related to sql

Examples related to oracle