How can I add leading zeros to dates in Oracle?

Question

I need to add leading zeros to a number if it is less than two digits and combine two such numbers into single one without space between them.

My Attempt:

select ( extract (year from t.Dt)
         || to_char(extract (month from t.Dt),'09')
         || to_char(extract (day from t.Dt),'09') ) as dayid 
  from ATM_FACTS t;

Result:

So, my problem is how can I remove the space in between month-year and month-day. I used

select ( extract (year from t.Dt)
         || to_number(to_char(extract (month from t.Dt),'09'))
         || to_number(to_char(extract (day from t.Dt),'09')) ) as dayid 
  from ATM_FACTS t;

but the leading zeros disappear.

Answer 1

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_facts

To 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.

Answer 2

Is t.Dt a DATE? You can format them in one single to_char statement:

to_char(t.Dt, 'YYYYMMDD')