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)
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;
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 1  Reply