• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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