Solved

retrieve the records between fromdate and todate

Posted on 2004-09-15
13
1,081 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

832 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