How to sort a db2 column containing date in char format???
Moderators: Kalicharan, Moderator Group
-
- Member
- Posts: 4
- Joined: Thu May 19, 2011 10:02 pm
- Location: India
How to sort a db2 column containing date 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.
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.
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.
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
-
- Member
- Posts: 4
- Joined: Thu May 19, 2011 10:02 pm
- Location: India
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
if you want the results of your cursor (ORDER BY) to be affected by the date,
you need to change your cursor declaration.
don't have a db2 available to test but, something like this:
your current cursor declaration:
new cursor declaration:
you need to change your cursor declaration.
don't have a db2 available to test but, something like this:
your current cursor declaration:
Code: Select all
DECLARE my_cursor CURSOR FOR SELECT
column1,
column2,
non_y2k_char_date_column,
column4
from your_table
where ...
order by column1,column2
Code: Select all
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
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
-
- Member
- Posts: 4
- Joined: Thu May 19, 2011 10:02 pm
- Location: India
-
- Member
- Posts: 4
- Joined: Thu May 19, 2011 10:02 pm
- Location: India
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page