CODEWITHSUNDEEP
sqloracle-databaseplsql
Clos
Clos

Reputation: 27

Oracle SQL fails when variable in where clause

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

Upvotes: 0

Views: 294

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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

Upvotes: 1

Related Questions