# Convert Date to Absolute Date

Author Message
Steve Ironmonger

New User

Joined: 19 Oct 2015
Posts: 15
Location: UK

 Posted: Tue Dec 15, 2015 3:57 pm Hi Guys, wondering if you can help. Is there any way of converting a date (yyyymmdd) into an Absolute Date, I work for a bank and we use an absolute date in quite a lot of programs, but just wondered if there was any way of converting it in sort or icetool ? We define the absolute date as the number of days since 1st Jan 1900 So today (15th Dec 2015 -20151215) would be 42322 cheers Steve
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

 Posted: Tue Dec 15, 2015 4:52 pm You want to have a look at DATEDIFF. You're going to have to put the 1900 date into a temporary extension to the record to have access to it. Exactly the best way to use it depends on exactly what you want to do, both generally and case-by-case perhaps as well.
Steve Ironmonger

New User

Joined: 19 Oct 2015
Posts: 15
Location: UK

 Posted: Tue Dec 15, 2015 5:38 pm Hi Bill, As an example I want to convert 20151215 to 42322 42322 being the number of days since 1st Jan 1900 or 19000131 to 00031
Bill Woodger

Moderator Emeritus

Joined: 09 Mar 2011
Posts: 7311
Location: Inside the Matrix

Posted: Tue Dec 15, 2015 6:20 pm

Here's an example with converting a date to a number with DATEDIFF. To save adjusting by one, I used 18991231 not 19000101.
 Code: OPTION COPY   INREC OVERLAY=(10:C'18991231',                  20:1,8,Y4T,                   DATEDIFF,                    10,8,Y4T)                                 //SORTIN   DD * 19000131 20151215

The output is:

 Code: 19000131 18991231  +0000031 20151215 18991231  +0042352

An eight-position field with a leading sign is created as the result of DATEDIFF. The + indicates that the second field is larger (- would indicate the opposite). Offset by three to get your desired result (23,5,CH for instance).

You need to temporarily "extend" each record to contain the base date, and the resultant difference. You can abut the fields, I've just left padding to more easily read the output.

For a fixed-length record, you extend after the end of the record. For a variable-length record extend at the beginning, using BUILD:

 Code: INREC BUILD=(1,4,C'18991231',8X,5)

You have to return the data to its original size after using the DATEDIFF (can be in OUTREC or OUTFIL).
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Date conversion using SORT SYNCSORT 4 Date Conversion Y2T to Y2W SYNCSORT 8 Date format conversion DFSORT/ICETOOL 4 Hex Value - vb file replace old date ... DFSORT/ICETOOL 14 COnvert a column with mix of hex ,cha... DB2 5
Search our Forums: