How can I add leading zeros to dates in Oracle?
It doesn't look like you want to add leading zero's, it looks like you're not converting your date to a character in exactly the way you want. The datetime format model of TO_CHAR() is extremely powerful, make full use of it.
select to_char(dt, 'yyyymmdd') as dayid from atm_factsTo actually answer your question you can use a number format model with TO_CHAR() to pad with leading 's.
For instance, the following returns 006
select to_char(6, 'fm009') from dual;You can use the format model modifier fm, mentioned in the docs above, to remove leading spaces if necessary.