I am typing SQL queries to get some data from a software tool which is based on an Oracle database. I am using the typical SELECT-statement.
Now, in my SQL-query I am using at different places the date "02.05.2012". Is there a way to define a variable date_string at the beginning and then use at all relevant places this variable?
This would simplify things a lot. Thanks for all hints and tips!
You might try to rewrite your query to return the literal from an inline view ...
select
my_date,
...
from(
select to_date('02.05.2012','DD.MM.YYYY') my_date from dual),
table2
where
some_column <= my_date
What you look for is a bind variable.
select to-date(:date, 'dd.mm.yyyy') date1
, to-date(:date, 'dd.mm.yyyy') + 1 date2
from dual
On runtime you need to pass the value to the bind variable. It all depends on your programming language how to bind the variable, but there is plenty documentation for that. DEFINE only works if you use sql*plus, and that's usually not the case inside a "software tool" :)
EDIT:
I'm beginning to understand now. It's just a textarea where you can enter a query and it will execute it and return the result. In that case you either write some complicated pl/sql code, or enter all the dates manually, or use a cross join with a select from dual:
with (select to_date('02.05.2012', 'dd.mm.yyyy') my_date from dual) d
select *
from some_table t
cross join d -- no ON required
If you want to select using the current date you can use sysdate.
Using SQLPLUS you can define your own variables:
SQL> define mydate ="01-05-2012"
SQL> select to_date('&mydate','DD-MM-YYYY') from dual;
01-MAY-12
try the following :
SELECT *
FROM table1
WHERE to_char(date1,'DD/MM/YYYY') = '&&date'
AND to_char(date2,'DD/MM/YYYY') = '&&date'
AND to_char(date3,'DD/MM/YYYY') = '&&date'
you will get a prompt to enter the value for the &&date , if you want to enter different values for each date, you should type &date instead of &&date
