All Forums Database
HanC 7 posts Joined 07/14
14 Jul 2016
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;

dnoeth 4628 posts Joined 11/04
14 Jul 2016

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'

 

Dieter

HanC 7 posts Joined 07/14
15 Jul 2016

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

You must sign in to leave a comment.