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
Try:
To_Date (SESSION_START_DATE_TIME, 'MM/DD/YYYY hh24:mi') >
To_Date ('12-Jan-2012 16:00', 'DD-MON-YYYY hh24:mi' )
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.
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
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.
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.
Source: Stackoverflow.com