How to sort a db2 column containing date in char format???

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
maverickgiant
Member
Posts: 4
Joined: Thu May 19, 2011 10:02 pm
Location: India

How to sort a db2 column containing date in char format???

Post by maverickgiant » Thu May 19, 2011 10:08 pm

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. 8)

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Thu May 19, 2011 10:26 pm

a quick idea,

select an additional column,
based on your CHAR date, cast it as a date column, then sort on it.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

maverickgiant
Member
Posts: 4
Joined: Thu May 19, 2011 10:02 pm
Location: India

Post by maverickgiant » Fri May 20, 2011 1:15 pm

Thanks, for the reply, the problem is that i cannot cast that as this happens within the cursor of a program.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Fri May 20, 2011 3:20 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:

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
new cursor declaration:

Code: Select all

DECLARE my_cursor CURSOR FOR SELECT
column1,
column2,
non_y2k_char_date_column,
case substr&#40;non_y2k_char_date_column,5,2&#41;
  when > char&#40;year&#40;current_date&#41;&#41; then '19' ||
                                 substr&#40;non_y2k_char_date_column,5,2&#41; ||
                                 substr&#40;non_y2k_char_date_column,3,2&#41; || 
                                 substr&#40;non_y2k_char_date_column,1,2&#41;
  else  '20' || 
          substr&#40;non_y2k_char_date_column,5,2&#41; || 
          substr&#40;non_y2k_char_date_column,3,2&#41; ||
          substr&#40;non_y2k_char_date_column,1,2&#41;
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.

maverickgiant
Member
Posts: 4
Joined: Thu May 19, 2011 10:02 pm
Location: India

Post by maverickgiant » Sat May 21, 2011 10:49 am

Thanks i will give it a try and let you know of the outcome

maverickgiant
Member
Posts: 4
Joined: Thu May 19, 2011 10:02 pm
Location: India

Post by maverickgiant » Sat May 21, 2011 9:36 pm

Great that worked. Thanks a lot :lol:

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Sun May 22, 2011 9:24 am

glad i was able to help, thx for responding
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

Post Reply

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



Other References
Mainframe Tools and others