CODEWITHSUNDEEP
sqloracle-database
Charles Flynn
Charles Flynn

Reputation: 59

Converting a number of months to date string

I have a number of months.

I wish to represent them as a number of years and months in the format YYMM

e.g.

5 -> 0005
13 -> 0101
24 -> 0200

Does anyone know of a non-convoluted way to do this in Oracle SQL?

Upvotes: 1

Views: 65

Answers (3)

Fabio Fantoni
Fabio Fantoni

Reputation: 3167

select lpad(trunc(yourValue/12),2,'0')||lpad(mod(yourValue,12),2,'0') result
  from dual

Upvotes: 0

Boneist
Boneist

Reputation: 23588

A little bit of maths and some formatting are the way to go:

with sample_data as (select 5 num from dual union all
                     select 13 num from dual union all
                     select 24 num from dual union all
                     select 1400 num from dual)
select to_char(floor(num/12), 'fm9999909')||to_char(mod(num, 12), 'fm09') yrmn
from   sample_data;

YRMN       
-----------
0005       
0101       
0200       
11608

I included one that had more than 100 years just to show you how it might look; I don't know if that's a possibility in your case, or if you'd want the other values to be zero-padded since you didn't say.

Upvotes: 3

Burcin
Burcin

Reputation: 995

try this;

select right("0" + convert(varchar(20), convert(numeric(10,0), 37/12)), 2)
                 + right("0" + convert(varchar(20), 37%12), 2)

0301

I used "37" for example.

Upvotes: -1

Related Questions