date variable not parsing proerly in Stored Procedure

Database
Enthusiast

date variable not parsing proerly in Stored Procedure

Hi, can anyone please help? I'm expecting to get the same date '2016-02-01' displaying with below code, instead I got number 2013. so, it's doing the math: 2013 = 2016-2-1

By the way, why I'm doing the test code below? because another more complicated code didn't work and it turned out to be the date variable making trouble. 

replace procedure SP_TEST2() begin

declare max_avail_date char(10);

declare sql_str varchar(1000);

set max_avail_date ='2016-02-01';

set sql_str = 'create multiset volatile table ttt as (select '||cast(max_avail_date as date format 'yyyy-mm-dd')||

' as dt) with data on commit preserve rows';

execute immediate sql_str;

end;

drop table ttt;

CALL SP_TEST2();

sel * from ttt;

2 REPLIES
Junior Contributor

Re: date variable not parsing proerly in Stored Procedure

This is the resulting string:

SELECT  '2016-02-01' AS max_avail_date,
'create multiset volatile table ttt as (select '||cast(max_avail_date as date format 'yyyy-mm-dd')||
' as dt) with data on commit preserve rows';

result:
create multiset volatile table ttt as (select 2016-02-01 as dt) with data on commit preserve rows

If you want a date you better use:

'create multiset volatile table ttt as (select DATE '''||max_avail_date|| ''' AS dt) WITH DATA ON COMMIT PRESERVE ROWS'
Enthusiast

Re: date variable not parsing proerly in Stored Procedure

Thank you Dieter! It's working like a charm.