Q
Problem solve Get help with specific problems with your technologies, process and projects.

Searching for stored reservation dates in a database

I have table in the database storing reservation dates. I want to search for all reservations made in a period of months; that is, I need to find all the reservation made between any two months or a particular month.

My input will be something like:
"Jan-2005" or "Nov-2004 to Feb-2005".

If you were willing to edit a script every time you had to do this sort of query, you might write a script like this:

--  res_dt.sql -- Find reservations made in or between
--                two given months.

--  Dates are hard-coded in this script.  To do a query
--  for any two months, you have to change two lines below.
--  (Look for the comments on the right-hand side.)

--  Note: the first month IS included, the second one
--       IS NOT.

SELECT   *
FROM     reservation
WHERE    made_dt >= TO_DATE ( '01-Nov-2004'
--                                ^^^^^^^^ <== Change above
                            , 'DD-Mon-YYYY'
                            )
AND      made_dt <  TO_DATE ( '01-Mar-2005'
--                                ^^^^^^^^ <== Change above
                            , 'DD-Mon-YYYY'
                            )
ORDER BY made_dt
,        id;
There are three main problems with this:
  1. You have to edit a file (typing the months in question) and then say "@res_dt" in SQL*Plus. It would be much easier to just say something like "@res_dt Nov_2004 Feb-2005"
  2. You can't let users do this themselves. Someone is bound to make a editing mistake that will ruin the script. Also, two users trying to use it at the same time would get in each other's way (unless they had their own copies).
  3. Instead of typing the last month you want, you have to type the first month you don't want. That's confusing, plus you have to figure it out.

We can solve problems 1 and 2 by using SQL*Plus substitution variables to pass the variable parts of the script. When SQL*Plus parses the script, it will substitute the value of the first parameter (from the command that called the script) for all occurrences of the string &1, and it will replace all occurrences of the string &2 with the second parameter. SQL*Plus shows exactly what it is doing by printing each line that includes substitution variables before and after making the substitutions, like this:

old   3: WHERE    made_dt >= TO_DATE ( '01-&1'
new   3: WHERE    made_dt >= TO_DATE ( '01-Nov-2004'
The line labeled "new" is what SQL*Plus will execute.

Problem 3 can be solved by using the ADD_MONTHS function. Making these changes, we have:

--  res_dt.sql -- Find reservations made in or between
--                two given months.

--  Call this script with two Mon-Year arguments,
--  like this:

--       @res_dt  Nov-2004  Feb-2005

--  All reservations made in either month, or
--  in months between them, will be shown.


SELECT   *
FROM     reservation
WHERE    made_dt >= TO_DATE ( '01-&1'
                            , 'DD-Mon-YYYY'
                            )
AND      made_dt <  ADD_MONTHS ( TO_DATE ( '01-&2'
                                         , 'DD-Mon-YYYY'
                                         )
                               , 1
                               )
ORDER BY made_dt
,        id;

This script takes two arguments. To run this script for a single month, you still have to pass two arguments, like "@res_dt Jan-2005 Jan-2005". This will produce the correct results, but it's not very convenient. Using optional arguments in a SQL*Plus script is a separate question.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close