Solved

Oracle datetime query question

Posted on 2009-04-06
2
821 Views
Last Modified: 2013-12-19
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
Comment
Question by:heyday2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 200 total points
ID: 24083468
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 24083881
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question