Simple Query Question

[marksmithy69]

Hello everyone. I have a table with a DATE column, which defaults to the sysdate during inserts etc.. It's format is 4/26/2007 3:26:22 PM. How can I select all records from this table, where this DATE is between two prompted dates? I am a newbie, so I apologize for the simple question. Thanks a lot.

 

[SantaMufasa]

Mark,

Actually, if a column is of type DATE, then there is no actual format, per se. Oracle stores DATEs in its own internal format, but we, as users, specify the external appearance of DATEs.

Here are some sample data:

select last_name, to_char(start_date,'mm-dd-yyyy') "Date Started"
from s_emp
order by start_date;

LAST_NAME                 Date Start
------------------------- ----------
Velasquez                 03-03-1990
Ropeburn                  03-04-1990
Ngao                      03-08-1990
Smith                     03-08-1990
Quick-To-See              04-07-1990
Biri                      04-07-1990
Menchu                    05-14-1990
Magee                     05-14-1990
Patel                     10-17-1990
Chang                     11-30-1990
Urguhart                  01-18-1991
Nozaki                    02-09-1991
Sedeghi                   02-18-1991
Havel                     02-27-1991
Dancs                     03-17-1991
Schwartz                  05-09-1991
Markarian                 05-26-1991
Nagayama                  06-17-1991
Newman                    07-21-1991
Patel                     08-06-1991
Dumas                     10-09-1991
Giljum                    01-18-1992
Nguyen                    01-22-1992
Maduro                    02-07-1992
Catchpole                 02-09-1992

25 rows selected.

In SQL*Plus, if you wish to prompt for values, you must use

1) the "ACCEPT <label> PROMPT <prompt message>" command pair, and
2) do so in a script (i.e., you cannot "copy-and-paste" the commands to the SQL*Plus prompt.

Below, then, is a SQL*Plus script (that I named, "tt_428.sql"), which contains sample code (querying against the sample data, above) to do what you specified:

set verify off
accept beg_dt prompt "Enter the beginning date of the range (mm-dd-yyyy): "
accept end_dt prompt "Enter the end date of the range (mm-dd-yyyy): "
select last_name, to_char(start_date,'mm-dd-yyyy')
from s_emp
where start_date between  to_date('&beg_dt','mm-dd-yyyy') and 
                          to_date('&end_dt','mm-dd-yyyy')
order by start_date
/
*********************************************************************

And here is a sample invocation of the "tt_428.sql" script:

SQL> @tt_428
Enter the beginning date of the range (mm-dd-yyyy): 02-01-1991
Enter the end date of the range (mm-dd-yyyy): 04-30-1991

LAST_NAME                 TO_CHAR(ST
------------------------- ----------
Nozaki                    02-09-1991
Sedeghi                   02-18-1991
Havel                     02-27-1991
Dancs                     03-17-1991

Let us know if this explains how to prompt for DATE ranges from SQL*Plus.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Nice script Dave, but I want to bring up one issue with mark. A date column also contains time. What this means is that if you have a row with a date stamp of 01/01/2007 11:45.30 and you wanted to see every row between 01/01/2007 and 01/01/2007, this row would not be returned because the a date without a time element is the same as midnight for the date. If you wanted to make sure that rows like this are returned, then a simple rewrite of Dave's excellent code would be

set verify off
accept beg_dt prompt "Enter the beginning date of the range (mm-dd-yyyy): "
accept end_dt prompt "Enter the end date of the range (mm-dd-yyyy): "
select last_name, to_char(start_date,'mm-dd-yyyy')
from s_emp
where trunc(start_date) between  to_date('&beg_dt','mm-dd-yyyy') and
                          to_date('&end_dt','mm-dd-yyyy')
order by start_date

The reason that this would work is because the trunc function removed the time portion of the date field.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Absolutely correct, Bill. Thanks for the enhancement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]