SQL Script

5 pts.
Tags:
Date calculation
SQL scripts
I am trying to write a sql script that will ask for a date range each time it's run. How do I do this?

Answer Wiki

Thanks. We'll let you know when a new response is added.

select * from analyst_actions
where last_updated between to_date(‘&start’,’yyyy-mm-dd’) and to_date(‘&endt’,’yyyy-mm-dd’)

If the end-user executes the query on SQL*Plus (or TOAD), substitute variables will work fine.

Use:

select * from analyst_actions where last_updated between ‘&start’ and ‘&end’;

SQL*Plus will prompt for START and END variables. User must enter dates in Oracle default date format i.e. DD-MON-YY.

ACCEPT StartDate DATE FORMAT ‘DD/MM/YYYY’ PROMPT ‘Start Date (DD/MM/YYYY) -> ‘;
ACCEPT EndDate DATE FORMAT ‘DD/MM/YYYY’ PROMPT ‘End Date (DD/MM/YYYY) -> ‘;

select * from analyst_actions
where last_updated between to_date(‘&StartDate’, ‘DD/MM/YYYY’) AND to_date(‘&EndDate’, ‘DD/MM/YYYY’);

The problem is in which environment will you run this select statement.

If the user has on his machine installed SQL*Plus you can write a script as showed above adding also the connection data to the server under username and password.

But if the user has not installed SQL*Plus you have to use front end interface which will ask the user to enter the dates and invoke an stored procedure to do the select and return the result.

———–

The reason that I asked the question is because I didn’t want to assume the database platform.

If you are using Microsoft SQL Server as an example there isn’t a way to prompt the user for input.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What database platform are you use? Microsoft SQL Server, MySQL, Oracle, DB2, etc?
    66,295 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following