[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

Oracle datetime query question

A junior question: I have some records with the saved_date filed: 03/04/2009, but when I run below query, no result displayed:
select * from table_name where saved_date = to_date('03/04/2009','mm/dd/yyyy')

When I changed to:
select * from table_name where saved_date >= to_date('03/04/2009','mm/dd/yyyy')

Then the three records of that day is shown. Could anyone let me know why the second ">=" query only returns the record of particular date instead of all the records after that date and how I could get the date query for a particular date?

Thanks.
0
heyday2004
Asked:
heyday2004
2 Solutions
 
lwadwellCommented:
Hi heyday2004,

there is possibly a time component in the field in the database - it is used in the comparison.  Hence the >= will return results as the TO_DATE() will return a date with a time of midnight a.m..  If you want to do an = test without time, try

select * from table_name where TRUNC(saved_date) = to_date('03/04/2009','mm/dd/yyyy')

lwadwell
0
 
sdstuberCommented:
the above query from lwadwell should work if you do have time in your value
but, if you have indexes on the saved_date column, I suggest writing the query like this instead.

select * from table_name where saved_date >= to_date('03/04/2009','mm/dd/yyyy') and saved_date < to_date('03/05/2009','mm/dd/yyyy')

using trunc on the column will negate the use of normal indexes
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now