I'm trying to run a plsql query in SQL Developer, and I'm using variables.
I try to run the following to see how variables work:
define FromDate varchar2 = '02-03-2019'
select &&FromDate from dual;
-- OR
define FromDate = '02-03-2019'
select &&FromDate from dual;
This is the result in both cases:
Why is the result "-2020" and how would I be able to use FromDate in something like this?
select * From TABLE where test_date >= to_date(&&FromDate, 'MM-DD-YYYY')
You are mixing 2 different concepts here. DEFINE is a SQLPLUS directive that takes the basic form var=text. When used SQLPLUS does a string replacement for var before submitting the query to the sql engine and processing wise SQL Developer emulates SQLPLUS. In your case then the sql engine only sees
Select 02-03-2019 from dual;
Which evaluates to -2020, the result you received.
Since the var you supply is seen by the sql engine after string replacement it does not know your intended use you need to make the conversion in you sql statement. So your statement should be:
select to_date(&&FromDate, 'MM-DD-YYYY') from dual;
This is pretty basic, you need to keep in mind just what component (app you 'talking' to for each statement.
The sql statement you posted as 'how to use' example is the correct usage.
Remember that substitution vars in SQL*Plus are exactly that - they substitute in whatever value you've given them without any punctuation. I'm sure you were expecting the statement to look like
select '02-03-2019' from dual;
after the substitution was made, but that's not how it works. When the substitution is made by SQL*Plus your SELECT statement becomes
select 02-03-2019 from dual;
and 2 minus 3 minus 2019 is -2020.
QED.
You might want to change your SQL to
select '&&FromDate' from dual;
It's resulting in an arithmetic expression
02 - 03 - 2019 = -2020
You should quote them if you wish to use it as a complete string in TO_DATE
select to_date('&&FromDate','mm-dd-yyyy') from dual;
old:select to_date('&&FromDate') from dual
new:select to_date('02-03-2019','mm-dd-yyyy') from dual
TO_DATE(
--------
03-02-19
