I am loading and transforming some legacy main frame data that stores dates in a Julian format number field, roughly an Oracle RRDDD date format. Some date between 2000 and 2009 drop the leading 0s, for example in 2009, giving a data like 931, which could be Jan 1, 1993 or Jan 31, 2009. I can stuff a leading zero(s) on the number field to give me '0931' in a char field, which my Oracle top_date will interpret as Jan 31 2009 with the correct mask 'RRDDD." BUT, I cannot find a way to pass my character sting '0931' which is a variable to my my Oracle to_date, which seems to want the enclosing single quotes, even from a character variable:
Thus
my_Julian_date = 345 -- Is February14 ,2003, leading 0's not available from legacy source |
|
SELECT TO_DATE('00345', 'RRDDD') FROM DUAL returns 14-FEB-03 OK |
SELECT TO_DATE(345, 'RRDDD') FROM DUAL returns 14_FEB-03 OK too |
|
but if I put my date as character into a variable, |
DECLARE my_date VARCHAR2(5); |
BEGIN |
my_date := '00345' ; |
SELECT TO_DATE( my_date, "RRDDD') ... fail to read my_date | /* please ignore that this select from DUAL won't work */
SELECT TO_DATE('|| my_date||', "RRDDD') ... | /* with the my_date variable, The issue is to_date accepting a variable value */
How can a pass a varchar2 or char VARIABLE with a VALUE like '00345' to TO_DATE?