Solved

retrieve the records between fromdate and todate

Posted on 2004-09-15
13
1,085 Views
Last Modified: 2008-10-30
hi

i want to write a query to retrieve the records between fromdate and todate;

if fromdate is '15-SEP-2004'

and todate is also '15-SEP-2004'
0
Comment
Question by:chaitu chaitu
[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
13 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 125 total points
ID: 12063404
select * from <table_name>
where trunc(<date_column>) = trunc(to_date('15-SEP-2004', 'DD-MON-YYYY'));
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 12063462


shouldn't  i use ' between' keyword??

in the query u have written

where should i incorporate fromdate column and todate column in the query

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12063496
Ok, if fromdate and todate is the same, then TRUNC will truncate off the time portion of the date.

so they will all come to '15-SEP-2004 00:00:00'
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 20

Author Comment

by:chaitu chaitu
ID: 12063526
if the fromdate is '15-sep-2004'  and todate is '16-sep-2004' then how will u modify the query


i want to write a common query to retrive the records irrespective if two dates r same or not;;


0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12063538
then you are giving me wrong example in your first post, that's a special case.

but in general it will be like this:
select * from <table_name>
where <date_column> between fromdate and todate;
0
 
LVL 6

Expert Comment

by:Jankovsky
ID: 12063564
select * from <table name> where <date column> between :fromdate and :todate; --in the case of exact time specification

select * from <table name> where <date column> between :fromdate and :todate+0.99999421; --in the case of the fromdate and todate parameters in the day precision and exact date column
0
 
LVL 20

Author Comment

by:chaitu chaitu
ID: 12063622


what it means :todate+0.99999421;
0
 
LVL 9

Expert Comment

by:konektor
ID: 12063655
DATE column granularity is 1 second, so u have to select ... between ... 15-SEP-2004 00:00:00 to 15-SEP-2004 23:59:59
Jankovsky's 0.99999421 represents + 1 day - 1 second

u can also :
select * from <table> where trunc(<column>,'dd') = to_date('15-SEP-2004','dd-mon-yyyy'), but if u have build index on date column, this select will not use it.
0
 
LVL 15

Expert Comment

by:andrewst
ID: 12063781
I'd rather have:

<date column> >= :fromdate and <date column> < :todate

than use that "magic number" 0.99999421 !
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12064126
select * from mytab
where trunc(<date_column>)  between to_date('From_Date_Param1','dd-MON-yyyy') and
to_date('From_Date_Param2','dd-MON-yyyy')

OR

select * from mytab
where <date_column>  between to_date('From_Date_Param1','dd-MON-yyyy') and
to_date('From_Date_Param2','dd-MON-yyyy')+1-1/24/60/60
/
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

632 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