N/APosted on - 08/15/2011
I am writing one query:
select * from user where logged_in_time='15aug2011 00:00:00' ;
as logged_in_time is a timestamp field and I want to list out all the users who logged in at this time.But it is not displaying any output, whereas if I write:
select * from user;
and try to find it manually, rows are present with this logged_in_time. Please help.
How to compare a time stamp field in where clause?
Timestamp in SQL is a bit tricky. It is a very fantastic feature which oracle has come up with. Remember, while comparing such values be careful of your data format. It means the format of your data must match to that one written in your query. For instance, Timestamp column has generally this format 01 15:00:00.000000 -5:00or
(2001-12-01 20:00:00.000000 GMT) so try to write a query accordingly.
As the query below is written to give you an idea.
SQL> select *
2 from myTable
3 where starts = TIMESTAMP '2001-12-01 15:00:00.000000 -5:00';
Hope it answers the question.