Date conversion question



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

Date conversion question

Postby dohara » Mon Jun 21, 2010 11:53 pm

I have the following in my sortin file: DATE: 20 JUN 2010

Is it possible to convert this date to 2010-06-21 and also add that it is the 25th week of the year?
so the output file would look like:
2010-06-21,25

Both the input and output files are LRECL=80,BLKSIZE=800,RECFM=FB

thanks,
David
dohara
 
Posts: 42
Joined: Thu Apr 09, 2009 3:15 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Date conversion question

Postby Bala1 » Wed Jun 23, 2010 4:55 pm

Hi Dohara,
I am not sure if a REXX code can solve your problem. If you can execute the below REXX code then you can get the desired output.


/*******REXX*******************************************/       
CLEAR                                                           
A = DATE(S)                                                     
SAY A                                                           
YR=SUBSTR(A,1,4)                                               
MNT=DATE(M,A,S)                                                 
DT=SUBSTR(A,7,2)                                               
D1=DATE(D,A,S)                                                 
DY=DATE(W,A,S)                                                 
WEEKN = D1%7                                                   
DATEF =YR||'-'||MNT||'-'||DT                                   
SAY 'DATE IS:' DATEF 'WEEK NO:'WEEKN 'DAY OF THE WEEK:' DY     
_____________________
Cheers,
Bala
Bala1
 
Posts: 15
Joined: Tue Apr 06, 2010 7:07 pm
Location: London, United Kingdom
Has thanked: 0 time
Been thanked: 0 time

Re: Date conversion question

Postby skolusu » Wed Jun 23, 2010 10:40 pm

dohara,

What is the position of the date? It is easy to get the date transformed into format you need. However week number is going to be a little work. Do you need the weeknum in standard US format where the week begins with SUNDAY or ISO format?

bala1,

OP needs the date from the input file to be modified to Gregorian format and NOT the current date. Dividing by 7 to get the week number is NOT the right way to get the week number.
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 conversion question

Postby dohara » Wed Jun 23, 2010 11:14 pm

Hi guys

taking another read of my first post, it seems i did not provide the necessary info. I'm sorry about that.

So the thing is in this particular case using REXX is unfortunately not an option.
I can only use JCL and thus i was thinking if DFSORT or ICETOOL have got the power to do the date conversion.

In the input file i have the following: DATE: 21 JUN 2010
and this should be converted into 2010-06-21 also it would be great if the number of the given week could be calculated from this date so the output should
look like:

2010-06-21,25

I need the week number in ISO format - week starting with Monday.
As you say deviding the number of the given day of the year with 7 is unfortunately not the right way to get around to finding the week number.
It would only work if January 1 was always monday - as it's not the case, it's quite a bit of thinking to do it even in rexx.
eg. this year - 2010 - Jan 1 was friday and that week was the 53th week of the last year. The first week of this year starts with Jan 4, which is the first monday in 2010.

I'm not aware of any existing REXX function that calculates the week number. If you know one, please let me know.
I used to spend hours to write my own. It's working but it's nasty. Would be happy to use a standard one.

thanks,
David
dohara
 
Posts: 42
Joined: Thu Apr 09, 2009 3:15 pm
Has thanked: 0 time
Been thanked: 1 time

Re: Date conversion question

Postby Bala1 » Thu Jun 24, 2010 12:15 am

Hi Kolusu,

Its possible to convert any date to the required format.
/*******REXX*******************************************/       
CLEAR
Say 'Provide the date'
Pull A                                                           
A = DATE(S)                                                     
SAY A         

And for your kind information the calculation WEEKN varible I have used the integer divide arithmetic operators (%) and hence it would be the no of weeks completed for that particular date...
:)

Hi David,
My apologies for not providing you the exact approach not an expert in ICETOOL :( , but you can execute rexx through JCL. which can have an PS file as an input and can write into an output file.
_____________________
Cheers,
Bala
Bala1
 
Posts: 15
Joined: Tue Apr 06, 2010 7:07 pm
Location: London, United Kingdom
Has thanked: 0 time
Been thanked: 0 time

Re: Date conversion question

Postby skolusu » Thu Jun 24, 2010 3:39 am

Bala1 wrote:Hi Kolusu, Its possible to convert any date to the required format.


Bala1,

I am aware of the fact that you can read an input dataset and perform the conversion in rexx. But your posted example doesn't show that. And REXX is a CPU HOG when you have millions of records to process.
And for your kind information the calculation WEEKN varible I have used the integer divide arithmetic operators (%) and hence it would be the no of weeks completed for that particular date...
:)


*Sigh* The week number isn't that easy as you think. For example take January 1st 2010 . According to you it is week 0. But in reality according ISO standards it is week 53.
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Date conversion question

Postby Thomas Ramseier » Mon Sep 20, 2010 11:52 am

Hi Bala1

Appended is a batchjob that lets you get the week-number of a given date by using DFSORT:

I'm sure there are easier ways to do it, but it seems to work.

Cheers

Thomas
weekjt01.txt
kwtest01.txt
You do not have the required permissions to view the files attached to this post.
Thomas Ramseier
 
Posts: 16
Joined: Mon Sep 20, 2010 11:31 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post