Solved

retrieve the records between fromdate and todate

Posted on 2004-09-15
13
1,078 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
 
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
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 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.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now