Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

retrieve the records between fromdate and todate

Posted on 2004-09-15
13
Medium Priority
?
1,089 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 500 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Suggested Courses

916 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