function not found – 2 questions
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)
Software/Hardware used:
Software/Hardware used:
ASKED:
May 4, 2006 8:52 PM
UPDATED:
May 5, 2006 12:27 PM
Answer Wiki:
(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)
To see all answers submitted to the Answer Wiki: View Answer History.
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;