I have the following under mycolumn in an Oracle DB
How can I select some values using where in Oracle SQL?
I tried the following
SELECT to_date(mycolumn, '%Y-%m-%d-%H.%i.%s.%f') FROM mytable
WHERE to_date(mycolumn, '%Y-%m-%d-%H.%i.%s.%f') > timestamp '2013-02-14 02:28:37.404' AND to_date(mycolumn, '%Y-%m-%d-%H.%i.%s.%f') < timestamp '2013-03-14 02:28:37.404' ;
>Solution :
If mycolumn column’s datatype is timestamp (should be; I hope you aren’t storing those values into a varchar2 column), then you’d e.g.
select *
from mytable
where mycolumn > to_timestamp('2013-02-14 02:28:37.404', 'yyyy-mm-dd hh24:mi:ss.ff3');
In other words, don’t touch mycolumn and provide valid format model for value you’re comparing it with.
If it really is a string (in a varchar2 column), then do the same for both values, e.g.
where to_timestamp(mycolumn, 'yyyy-mm-dd-hh24.mi.ss.ff6') >
to_timestamp('2013-02-14 02:28:37.404', 'yyyy-mm-dd hh24:mi:ss.ff3')