Invalid Time Literal in Teradata Dynamic SQL

Database
Teradata Employee

Invalid Time Literal in Teradata Dynamic SQL

replace procedure common.sp_test_sp(in p1 varchar(35), in p2 integer)
begin
call dbc.sysexecsql('insert into common.sp_test
values (1, cast(''||:p1||'' as timestamp(3)),cast(''||:p1||'' as timestamp(3)) + interval '''||:p2||''' day');
end

call common.sp_test_sp('2017-03-17 11:37:52.290',3)

 

This is my sp and call statement....but when i execute call statement i get invalid interval literal error. Let me know correct syntax for passing parameter value for interval

3 REPLIES
Teradata Employee

Re: Invalid Time Literal in Teradata Dynamic SQL

call dbc.sysexecsql('insert into common.sp_test
values (1, cast('''||p1||''' as timestamp(3)),cast('''||p1||''' as timestamp(3)) + interval '''||p2||''' day)');
end

Senior Apprentice

Re: Invalid Time Literal in Teradata Dynamic SQL

You need to check the string passed to sysexecsql if it's valid syntax, in your case there are some single quotes & a TRIM missing:

CALL dbc.sysexecsql('insert into sp_test
values (1, cast('''||:p1||''' as timestamp(3)),cast('''||:p1||''' as timestamp(3)) + interval '''||Trim(:p2)||''' day)');
end

Of course you could simply define the IN params as Timestamp & Interval to avoid then casting.

Teradata Employee

Re: Invalid Time Literal in Teradata Dynamic SQL

Thank you Fred and Dnoeth, it worked for me.