Error when calling an UDF

Database

Error when calling an UDF

Hi,

I am facing a weird error when calling a SQL UDF. The weird stuff is that I do not get any error when calling the return expression directly: below my simple code

REPLACE FUNCTION "demo"."FIRST_DAY_IN_PERIOD" (
"given_period" VARCHAR(8) CHARACTER SET LATIN)
RETURNS DATE
SPECIFIC "FIRST_DAY_IN_PERIOD"
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
CALLED ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER INLINE TYPE 1
RETURN cast('01-'||given_period as DATE format 'DD-MMM-YYYY');

select demo.FIRST_DAY_IN_PERIOD('JAN-2005'); -ERROR: Executed as Single statement. Failed [2665 : 22007] Invalid date.

select cast('01-'||'JAN-2005' as DATE format 'DD-MMM-YYYY'); -RETURNS:2005-01-01

I just don't get it... why my expression works whilst my UDF does not?? What does it mean "Invalid date"??

Thanks,
Lorenzo
Tags (1)
2 REPLIES
kri
Fan

Re: Error when calling an UDF

yes u defined month and year better to define date or check for date format it will work out
Teradata Employee

Re: Error when calling an UDF

Hi,

Firstly, what exact format are you returning the output in?

Secondly, do you really require a UDF for finding 1st day of a month?

Regards, MAC