Solved

Oracle datetime query question

Posted on 2009-04-06
2
816 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

739 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