How to sort a db2 table column, containing date stored in char format.
i have a column which is not Y2K complaint, the values are stored in char format like ddmmyy ( i know its totally dumb), i need to sort based on ascending or descending order.
Software/Hardware used:
051193 -> 5 nov 1993
140308 -> 14 march 2008
141211 -> 14 december 2011
Now how do i sort this column (year wise) while extracting?
i cannot sort based on last two char using substring, as 08 < 93 but we know that 2008 is not less than 1993. i tried to split into two halves one before 2000 and one after 2000 still i am not sure how to put in one cursor as such. Can someone please help me with this???
Any ideas on how to sort this column datewise would be welcome.
Software/Hardware used:
ASKED:
May 19, 2011 5:01 PM
UPDATED:
May 21, 2011 4:40 PM
Answer Wiki:
DECLARE my_cursor CURSOR FOR SELECT
column1,
column2,
non_y2k_char_date_column,
case
when substr(non_y2k_char_date_column,5,2) > char(year(current_date)) then '19' ||
substr(non_y2k_char_date_column,5,2) ||
substr(non_y2k_char_date_column,3,2) ||
substr(non_y2k_char_date_column,1,2)
else '20' ||
substr(non_y2k_char_date_column,5,2) ||
substr(non_y2k_char_date_column,3,2) ||
substr(non_y2k_char_date_column,1,2)
end as sortable_date,
column4
from your_table
where ...
order by sortable_date
To see all answers submitted to the Answer Wiki: View Answer History.
Convert the ddmmyy to a true date with this expression:
DATE( TRIM (CHAR(mo)) || ‘/’ ||
TRIM (CHAR(da)) || ‘/’ ||
TRIM (CHAR((cn * 100) + yr)))
Then sort on the column number.
If you have potential invalid dates, you can use the same expression to set up a case statement with a valid range of dates and default substitution when invalid.
Gary
I should add than if you do not have “cn”, you will also have to handle that with case/range/default logic.
Thanks for the reply, can you explain it a bit more?
Thanks for the reply, however i found another solution:
DECLARE my_cursor CURSOR FOR SELECT
column1,
column2,
non_y2k_char_date_column,
case substr(non_y2k_char_date_column,5,2)
when > char(year(current_date)) then ’19′ ||
substr(non_y2k_char_date_column,5,2) ||
substr(non_y2k_char_date_column,3,2) ||
substr(non_y2k_char_date_column,1,2)
else ’20′ ||
substr(non_y2k_char_date_column,5,2) ||
substr(non_y2k_char_date_column,3,2) ||
substr(non_y2k_char_date_column,1,2)
end as sortable_date,
column4
from your_table
where …
order by sortable_date