Convert date from YYDDD to MM-DD-YYYY format using sort.



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

Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby arpitpatel01 » Fri Apr 10, 2015 6:40 pm

Hi,
Need ur help.

I have an input date which is in YYDDD format. I need to convert it to MM-DD-YYYY format using SORT.

Input:
14012
19012
16156

Output:
01-12-2014
01-12-1919
06-04-1916

Kindly help!

Thanks in advance!
arpitpatel01
 
Posts: 15
Joined: Wed Mar 25, 2015 9:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

 

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby arpitpatel01 » Fri Apr 10, 2015 6:56 pm

The required job ia being done using either of the below sort cards:

SORT FIELDS=COPY
INREC BUILD=(1:1,5,Y2T,TOGREG=(Y4T(-)))


OR

SORT FIELDS=COPY
INREC BUILD=(1:1,5,Y2T,DT=(4MD/))


But the issue is that for the input date as 19012, it is giving output as 2019-05-03. But i want 1919-05-03 in output.
arpitpatel01
 
Posts: 15
Joined: Wed Mar 25, 2015 9:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby BillyBoyo » Fri Apr 10, 2015 9:06 pm

I've a feeling that because your date range is 102 years, you won't be able to do it like that anyway. Have a little search in the DFSORT documentation, for the word "window". I think you'll need additional coding. You must have some formula for knowing, already. Does it have to change each year?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby arpitpatel01 » Mon Apr 13, 2015 11:39 am

@ BillyBoyo: Yes... it will change every year.
The functionality would be same as per GOADTE01 date converter utility with no offset range defined for date conversion.
arpitpatel01
 
Posts: 15
Joined: Wed Mar 25, 2015 9:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby BillyBoyo » Mon Apr 13, 2015 11:49 am

Well, if you have a range of more than 100 years, strictly, more than 99 years, you need additional information to be able to know which century to attach to a date. If your range is 1916-2015, then when a 12 appears as the year, DFSORT is going to treat it as 2012. If there is other information on the record to identify it as not 2012, you have to use that information yourself. How did you know the date was wrong? Use that.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby arpitpatel01 » Mon Apr 13, 2015 2:25 pm

Well, that was checked using GOADTE01 utility in Workbench.
arpitpatel01
 
Posts: 15
Joined: Wed Mar 25, 2015 9:32 am
Has thanked: 0 time
Been thanked: 0 time

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby enrico-sorichetti » Mon Apr 13, 2015 3:00 pm

play around with
OPTION Y2PAST=

using different window year to find out a behaviour acceptable to Your environment
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby NicC » Mon Apr 13, 2015 3:53 pm

I googled GOADTE01 and got 0 hits so I guess that is some internal utility unknown to anyone except those that work in your company so mentioning it here, by name, is pointless!
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Convert date from YYDDD to MM-DD-YYYY format using sort.

Postby arpitpatel01 » Fri Apr 24, 2015 8:13 pm

Yes NicC... you are correct it is being used in our company. Thanks

Anyways, i got the approach for the abvoe requirement...
Whenever the date conversion is happening... the lowest cut-off date is calculated as (CURRENT-YEAR - 99) and then it is compared with the input date and then the date conversion takes place.

Will post the actual solution once we are done with its build...

Thanks all for your help! :)
arpitpatel01
 
Posts: 15
Joined: Wed Mar 25, 2015 9:32 am
Has thanked: 0 time
Been thanked: 0 time


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post