Search code examples
sqldb2db2-luw

No authorized routine named "VARCHAR_FORMAT" of type "FUNCTION" having compatible arguments was found

Relevant part of my query:

SELECT 
    ppt_id AS " PPT ID "
    ,VARCHAR_FORMAT(CDT_DT, 'MM/DD/YYYY') AS " DATE FROM "
...
INNER JOIN hs.cdt_clm_dt ON cdt_clm_id = clm_id

Here is the table in question:

db2 => describe table hs.cdt_clm_dt ;

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CDT_CLM_ID                      SYSIBM    INTEGER                      4     0 No    
CDT_DT_QUAL                     SYSIBM    CHARACTER                    3     0 No    
CDT_DT                          SYSIBM    DATE                         4     0 No    
CDT_TO_DT                       SYSIBM    DATE                         4     0 Yes   
CDT_TM                          SYSIBM    TIME                         3     0 Yes   
CDT_TM_TO                       SYSIBM    TIME                         3     0 Yes   

  6 record(s) selected.

I receive the following error:

SQL0440N  No authorized routine named "VARCHAR_FORMAT" of type "FUNCTION" 
having compatible arguments was found.  SQLSTATE=42884

Any ideas why I might be seeing this?

EDIT: This function is available to me:

db2 => SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP, 'MM/DD/YYYY') FROM SYSIBM.SYSDUMMY1;

1                                                                                                                                                                                                                                                             
-------------------------------
11/18/2014
Solution

  • In DB2 9.5 VARCHAR_FORMAT() requires a TIMESTAMP argument, while you are supplying a DATE. You can try:

    ...VARCHAR_FORMAT(TIMESTAMP(CDT_DT,'00.00.00'), 'MM/DD/YYYY')

    Some remarks:

    • Does it really make sense to store the DATE and TIME components separately?
    • Might it be better to delegate formatting of output to the presentation layer?