Solved

retrieve the records between fromdate and todate

Posted on 2004-09-15
13
1,084 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 52
Oracle Function Based Index and Non-Function Based Index on Same Column 36 37
Oracle performance tuning 2 55
Oracle Date add 9 33
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

734 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