date field conversions



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

date field conversions

Postby ricklennox » Sun Jan 16, 2011 2:31 am

I've tried to convert a 4-byte binary date field to a Gregorian date using this method in my Inrec Build:
INREC BUILD=((1:1,4,BI,TO=PD, LENGTH=4),Y2V,TOGREG=Y4T(-))

I was expecting the first conversion in the statement (within the inner parenthesis) to convert the 4-byte binary value X'0001AE23' (representing a decimal value 110115) into X'0110115C', and then the combination of the "Y2V,TOGREG=Y4T(-)" would execute a second conversion to C'2011-01-15'. Unfortunately, the Y2V seems to want an explicit "p,m,Yxx,todate" format, and isn't accepting the first conversion. I'm curious if it can be done, as all of the examples in the SORTUGPG.pdf are of the "p,m,Yxx,todate" nature.

After a couple hours of banging this around, I've tried another method - converting the binary date value to a PD value in a preliminary Copy to a temporary file, then using this temp file in my JOINKEYS where the PD date can be converted to Gregorian prior to submission to the Join. This is a bit cumbersome, so if there's a simpler method to do this in a single step I'd love to hear it.

Thanks.
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time

Re: date field conversions

 

Re: date field conversions

Postby skolusu » Mon Jan 17, 2011 10:49 pm

ricklennox,

Nested expressions aren't allowed for date related arithmetic. You don't require 2 passes of data. You can just IFTHEN statements to modify and get the results. Also unless you are converting from Julian to Gregorian format, You dont have to use the TOGREG function to edit the date with separators. Use the following control cards.

//SYSIN    DD *                                           
  SORT FIELDS=COPY                                       
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,BI,M11,LENGTH=6)),   
  IFTHEN=(WHEN=INIT,BUILD=(1,6,Y2T(-)))                   
//*


This will create a 10 byte file with CCYY-MM-DD format depending on your Y2PAST. If you want you can override the Y2PAST with your desired year. ex:
OPTION Y2PAST=1990
This would give a century window of 1990-2089.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: date field conversions

Postby ricklennox » Mon Jan 17, 2011 11:15 pm

Thank you, Kolusu.. I'll try to implement your solution. I appreciate your help.
ricklennox
 
Posts: 11
Joined: Thu Dec 16, 2010 5:59 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post