function not found – 2 questions

pts.
Tags:
IBM DB2
SQL
I wrote a function to change a date in character *USA format to *ISO character format. There is a lot of data to insert and all the dates are in *USA format rather than ISO format. The function is created but running the statement, I am told the function does not exist. Why isn't the function found? (Question 1) Here's the code. DROP FUNCTION TIMR.U2O; CREATE FUNCTION TIMR.U2O(INUSA CHAR(10) ) RETURNS CHAR(10 ) LANGUAGE SQL SPECIFIC USA2ISO BEGIN DECLARE OUTISO CHAR(10); SET OUTISO = CONCAT(SUBSTR(INUSA, 7,4), '-') ; SET OUTISO = CONCAT(OUTISO, SUBSTR(INUSA, 1,5)) ; RETURN OUTISO ; END ; SELECT TIMR.U2O('12-31-1931') AS BDATE FROM TIMR.DISMSTP; I looked for an existing function that would do this but could find none. Is there a shorter way? (Question 2)
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

(1) I’m not sure what the SPECIFIC clause does. You might want to try removing that.
(2) You can do this all at once: RETURN SUBSTR(input,4,3)||SUBSTR(input,1,3)||SUBSTR(input,7)
—
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Sequential
    I would suggest changing this from SELECT TIMR.U2O('12-31-1931') AS BDATE FROM TIMR.DISMSTP; to SELECT TIMR.U2O('12-31-1931') AS BDATE FROM dual; Or you might want to check out this alter session set NLS_DATE_FORMAT='my_format'; But in the off chance you need the function to work. CREATE OR REPLACE FUNCTION U2O(INUSA CHAR) RETURN CHAR IS OUTISO VARCHAR2(30); BEGIN OUTISO := CONCAT(SUBSTR(INUSA, 7,4), '-') ; OUTISO := CONCAT(OUTISO, SUBSTR(INUSA, 1,5)) ; RETURN OUTISO; END; / SELECT U2O('12-31-1931') AS BDATE FROM dual;
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: