I just switched from SQL Server to Oracle and I'm confused about how to get results when I'm using a variable who's value isn't set based on a result from sql query. What on earth is wrong with this?
variable firstDate date;
variable secondDate date;
exec firstDate := '03/01/2019';
exec secondDate := '03/31/2019';
select
c.xent_id
FROM
lic c
WHERE
C.EXPR_DTE >= TO_DATE(firstDate,'MM/DD/YYYY')
AND C.EXPR_DTE <= TO_DATE(secondDate,'MM/DD/YYYY')
AND c.clnt_cde = 8801
You cannot define a DATE bind variable in SQL* Plus / SQL developer. It maybe defined as a VARCHAR2 and assigned a string and converted where needed. Also, note that you are missing colons at appropriate places before the bind variables. Check the query to see how it's used. You may put the same expressions in your where clause of the actual query.
variable firstDate VARCHAR2;
variable secondDate VARCHAR2;
exec :firstDate := '03/01/2019';
exec :secondDate := '03/31/2019';
select TO_DATE(:firstDate,'MM/DD/YYYY') dt1 , TO_DATE(:secondDate,'MM/DD/YYYY')
as dt2 from dual;
DT1 DT2
-------- --------
01-03-19 31-03-19
xent_id