I need to sort a varchar2 datatype column with date value in number format as YYMM to MMYY
The value ranges from
3
6
9
12
103
.
.
9909
9912
The value with 4 characters in length denotes the year between 1900 and 1999 less than 4 characters denotes the year starts with 2000 and above.
I tried using LPAD(fieldname, 4,'0') which returns value as
0003 this is nothing but 2000/03
0006 ------------------- 2000/06
0009
0012
0103 ------------------- 2001/03
.
.
9912 ------------------- 1999/12
how can i order the column based on the year value starting from 1900 in ascending order . Can anyone please provide me the solution....
You can prepend a century marker based on the length of the value:
select value,
case when length(value) = 4 then '19' else '20' end || lpad(value, 4, '0') as dt
from t
order by case when length(value) = 4 then '19' else '20' end || lpad(value, 4, '0');
VALUE DT
---------- ------
9909 199909
9912 199912
6 200006
9 200009
12 200012
103 200103
Or use the same thing and convert to a date, which defaults to the first day of each month:
select value, to_date(case when length(value) = 4 then '19' else '20' end
|| lpad(value, 4, '0'), 'YYYYMM') as dt
from t
order by to_date(case when length(value) = 4 then '19' else '20' end
|| lpad(value, 4, '0'), 'YYYYMM');
VALUE DT
---------- ----------
9909 1999-09-01
9912 1999-12-01
6 2000-06-01
9 2000-09-01
12 2000-12-01
103 2001-03-01
If you're only looking at dates within the Y2K-safe range of 1950-2049, you could skip the century part and use an RR date model instead, though since this will potentially cause you problems later there isn't really any advantage over using the length to prepend the century:
select value, to_date(lpad(value, 4, '0'), 'RRMM') as dt
from t
order by to_date(lpad(value, 4, '0'), 'RRMM');
VALUE DT
---------- ----------
9909 1999-09-01
9912 1999-12-01
6 2000-06-01
9 2000-09-01
12 2000-12-01
103 2001-03-01