to_char function in stored procedure

Database
DS
Enthusiast

to_char function in stored procedure

Hi All,

The below query works fine when i ran it in sql assistant.

sel cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01'||to_char(trim(extract( year from <table.date_column>)),'0999') as date )

from table;

when I run the same query with insert as below  in a stored procedure gives error as ' function 'TO_CHAR' does not exist'

insert into table (.............) as

sel (.....,cast(to_char(trim(extract( month from <table.date_column>)),'09')||'01'||to_char(trim(extract( year from <table.date_column>)),'0999') as date, ........ )

from table;

Please help me resolving it.

Thanks

6 REPLIES
Teradata Employee

Re: to_char function in stored procedure

The to_char function is an ODBC extension and is being translated by the client driver.

Something like this should work:

cast(cast(cast(extract( month from table.date_column) as format '99') as CHAR(2)) ||'01'||cast(cast(extract( year from table.date_column) as format '9999') as CHAR(4)) as date format'mmddyyyy')

So should

cast(cast(cast(table.date_column as format 'mmyyyy') as char(6)) as date format 'mmyyyy')

DS
Enthusiast

Re: to_char function in stored procedure

I managed using trunc as below

sel (.....,trunc(<table.date_column>,'RM'), ........ )

Anyways thanks a lot!

DS
Enthusiast

Re: to_char function in stored procedure

Can any one tell me what should be done to make to_char function work in stored procedure

Enthusiast

Re: to_char function in stored procedure

It looks like your system already has the Oracle UDF's installed, as it worked in SQL Assitant.

There may be some permission issues, or you may need to fully qualify the function call, check with your local handy dandy DBA.

Teradata Employee

Re: to_char function in stored procedure

TRIM returns VARCHAR. Even if you have TO_CHAR UDF (e.g. TD14), there is no specific function prototype for TO_CHAR that accepts VARCHAR arguments. So remove TRIM and pass the numeric value returned by EXTRACT.

Again, it is working in SQL Assistant only because the SQL you typed is not the SQL that is being sent to the database by the ODBC driver. Try it in BTEQ or in SQL Assistant with .NET provider and it will fail just as it does in the SP.

DS
Enthusiast

Re: to_char function in stored procedure

VandeBergB,

I tried by qualifing with TD_SYSFNLIB.

Doesn't worked.