CODEWITHSUNDEEP
sqlamazon-redshift
shaaz manjiyani
shaaz manjiyani

Reputation: 13

[Amazon](500310) Invalid operation: return type mismatch in function declared to return integer;

I am trying to Create SQL a function in Amazon Redshift.

Please tell me why am I getting this error and how can I solve the same:

error: Amazon Invalid operation: return type mismatch in function declared to return integer;

My code is:

create function calc_age (varchar)
returns int
stable
AS $$   
    select DATEDIFF(YY,to_date(substring($1,1,10),'yyyy-mm-dd'),to_date(GETDATE(),'yyyy-mm-dd'))
$$ Language sql;

Upvotes: 1

Views: 562

Answers (1)

Paul Karam
Paul Karam

Reputation: 4210

According to Amazon's redshift documentation, DATEDIFF function return type is BIGINT.

I would suggest you try to convert that to INT using the following query: 

select CONVERT(integer, DATEDIFF(YY,to_date(substring($1,1,10),'yyyy-mm-dd'),to_date(GETDATE(),'yyyy-mm-dd')))

To read more about the CONVERT function, check This

Upvotes: 2

Related Questions