11

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:

enter image description here

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.

Wouter
  • 1,829
  • 3
  • 28
  • 34
Jivan
  • 1,300
  • 6
  • 21
  • 33
  • i didn't really understand if you want or the zero's or not, but is you don't then maybe something like `select to_number(replace(to_char(t.Dt,'DS'),'/','')) from dual` – haki May 16 '13 at 11:02

2 Answers2

23

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • @Zane - it's polite to accept answers that helped solve your problem. – Bob Jarvis - Слава Україні May 16 '13 at 11:08
  • I would recommend reading the linked documentation @zane, the datetime format model for the 24 hour clock is `hh24` and for a minute is `mi`, so `to_char(dt, 'hh24mi')` – Ben May 16 '13 at 11:13
  • @BobJarvis, Thanks for your comment...I was about to do that – Jivan May 16 '13 at 11:17
  • to_char is not a solution, since it adds a leading space. A solution is mentioned here: http://stackoverflow.com/questions/23814518/oracle-adding-leading-zeros-to-string-not-number – Wouter Jan 19 '15 at 16:03
  • 1
    Not any more it doesn't @Wouter and the question may be asking about dates but it's still completely relevant to numbers and has been answered for numbers... – Ben Jan 19 '15 at 16:14
2

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

to_char(t.Dt, 'YYYYMMDD')
nvoigt
  • 75,013
  • 26
  • 93
  • 142