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

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

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  gt  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  gt  to date  12-Jan-2012 16 00    DD-MON-YYYY hh24 mi     select   from tableName2 where session start date time  gt  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

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

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

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

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

Examples related to sql

Examples related to oracle