How to sort a db2 table column, containing date stored in char format.

40 pts.
Tags:
AS/400 DB2
DB2 Table
IBM DB2
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.
 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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DoneThat
    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
    830 pointsBadges:
    report
  • DoneThat
    I should add than if you do not have "cn", you will also have to handle that with case/range/default logic.
    830 pointsBadges:
    report
  • Maverickgiant
    Thanks for the reply, can you explain it a bit more?
    40 pointsBadges:
    report
  • Maverickgiant
    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
    40 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following