There are multiple Date Functions by which you can reformat input dates.
- TOJUL=Yaa – converts to a Julian date without a separator (for example, P’2009007′).
- TOJUL=Yaa(s) – converts to a Julian date with a separator (for example, C’325-2008′).
- TOGREG=Yaa – converts to a Gregorian date without separators (for example, Z’091121′).
- TOGREG=Yaa(s) – converts to a Gregorian date with separators (for example, C’2009.09.21′).
- WEEKDAY=CHAR3 – converts to a 3-character day of the week (for example, C’WED’ for Wednesday).
- WEEKDAY=CHAR9 – converts to a 9-character day of the week (for example, C’THURSDAY ‘ for Thursday).
- WEEKDAY=DIGIT1 – converts to a 1-digit indicator for the day of the week (for example, C’2′ for Monday).
Current date as a character string
DATE1, &DATE1, DATE1(c), &DATE1(c), DATE2, &DATE2, DATE2(c), &DATE2(c), DATE3, &DATE3, DATE3(c), &DATE3(c), DATE4, &DATE4, DATE5 or &DATE5 can be used to generate a character string for the current date of the run.
Note: You can precede each of the operands in the table with an & with identical results. When a field is shorter than the character string it’s compared to, DFSORT truncates the string on the right. You can take advantage of this to compare a field to only part of the DATE4 timestamp when appropriate. For example:
INCLUDE COND=(1,13,CH,GT,DATE4)
The above condition will compare the field in positions 1-13 to the truncated DATE4 constant C’yyyy-mm-dd-hh’.
| Character Strings for Current DateFormat of Operand | Format of Constant | Example of Constant |
|---|---|---|
| DATE1 | C’yyyymmdd’ | C’20050621′ |
| DATE1(c) | C’yyyycmmcdd’ | C’2005/06/21′ |
| DATE2 | C’yyyymm’ | C’200506′ |
| DATE2(c) | C’yyyycmm’ | C’2005/06′ |
| DATE3 | C’yyyyddd’ | C’2005172′ |
| DATE3(c) | C’yyyycddd’ | C’2005/172′ |
| DATE4 | C’yyyy-mm-dd-hh.mm.ss’ | C’2005-06-21-16.52.45′ |
| DATE4 | C’yyyy-mm-dd-hh.mm.ss’ | C’2005-06-21-16.52.45′ |
| DATE5 | C’yyyy-mm-dd-hh.mm.ss.nnnnnn’ | C’2005-06-21-16.52.45.582013′ |
Future or Past date as a character string
DATE1+d, &DATE1+d, DATE1(c)+d, &DATE1(c)+d, DATE2+m, &DATE2+m, DATE2(c)+m, &DATE2(c)+m, DATE3+d, &DATE3+d, DATE3(c)+d or &DATE3(c)+d can be used to generate a character string for a future date relative to the current date of the run. d is days in the future and m is months in the future. d and m can be 0 to 9999.
DATE1-d, &DATE1-d, DATE1(c)-d, &DATE1(c)-d, DATE2-m, &DATE2-m, DATE2(c)-m, &DATE2(c)-m, DATE3-d, &DATE3-d, DATE3(c)-d or &DATE3(c)-d can be used to generate a character string for a past date relative to the current date of the run. d is days in the future and m is months in the future. d and m can be 0 to 9999.d is days in the future and m is months in the future. d and m can be 0 to 9999.
Current date as decimal number
DATE1P, &DATE1P, DATE2P, &DATE2P, DATE3P, or &DATE3P can be used to generate a decimal number for the current date of the run.
DATE1P+d, &DATE1P+d, DATE2P+m, &DATE2P+m, DATE3P+d, or &DATE3P+d can be used to generate a decimal number for a future date relative to the current date of the run. d is days in the future and m is months in the future. d and m can be 0 to 9999.
DATE1P-d, &DATE1P-d, DATE2P-m, &DATE2P-m, DATE3P-d, or &DATE3P-d can be used to generate a decimal number for a past date relative to the current date of the run. d is days in the past and m is months in the past. d and m can be 0 to 9999.
Date Functions: Date Formatting
YYYYMMDD to YYYYDDD format
The following job converts a ‘yyyymmdd’ date to a ‘yyyyddd’ date:
//S1 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD * 20090520 20100106 20100921 20081217 //SORTOUT DD SYSOUT=* //SYSIN DD * OPTION COPY INREC BUILD=(1,8,Y4T,TOJUL=Y4T) /* OUTPUT: 2009140 2010006 2010264 2008352
YYMMDD to YYYY-DDD format
INPUT: ABC 090520 DEF 100106 GHI 100921 JKL 081217 SYSIN: OPTION Y2PAST=1990 SORT FIELDS=(5,6,Y2T,A) OUTREC OVERLAY=(5:5,6,Y2T,TOJUL=Y4T(-)) OUTPUT: JKL 2008-352 ABC 2009-140 DEF 2010-006 GHI 2010-264
DDDYYYY to MM-DD-YYYY format
OPTION COPY OUTFIL OVERLAY=(1:1,4,Y4X,TOGREG=Y4W(-))
YYYY-MM-DD to MMDDYY format
SORT FIELDS=(1,10,CH,A) OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,10,UFF,TO=ZD,LENGTH=8)), IFTHEN=(WHEN=INIT,BUILD=(1,8,Y4T,TOGREG=Y2Y))
Converts MM/DD/YYYY to YYYYDDD format
OPTION COPY OUTREC IFTHEN=(WHEN=INIT, BUILD=(1,10,UFF,TO=ZD,LENGTH=8,9:11,3)), IFTHEN=(WHEN=INIT,BUILD=(1,8,Y4W,TOJUL=Y4T,9,3))
Date Functions: Validate Input Date
You can use TOGREG or TOJUL functions to identify invalid input dates. Dates with values outside of the valid range (for example, a month not between 01-12) will be shown as asterisks making them easy to identify.
Example: If you had the following input records with ‘yyyymmdd’ dates:
INPUT: Betten 20091021 Vezinaw 20091101 Casad 00000000 Boenig 20091325 Kolusu 20090931 Yaeger 20090731 You could use below control statements to display an additional column with asterisks for any invalid dates SYSIN: OPTION COPY OUTREC OVERLAY=(30:16,8,Y4T,TOGREG=Y4T) OUTPUT: BETTEN 20091021 20091021 VEZINAW 20091101 20091101 CASAD 00000000 00000000 BOENIG 20091325 ******** KOLUSU 20090931 ******** YAEGER 20090731 20090731
If you wanted to display only the records with invalid dates, you could use these control statements:
SYSIN: OPTION COPY OUTREC OVERLAY=30:16,8,Y4T,TOGREG=Y2T) OUTFIL INCLUDE=30,1,CH,EQ,C'*'),BUILD=(1,25) OUTPUT: BOENIG 20091325 KOLUSU 20090931
Date Functions: Calculate days between two date fields
Here is an example on how to calculate the number of days between two dates.
INPUT: 20101215 20101105 20110218 20100913 20110127 20110305 //S1 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD DSN=... input file //SORTOUT DD DSN=... output file //SYSIN DD * OPTION COPY INREC OVERLAY=(20:1,8,Y4T,DATEDIFF,10,8,Y4T) /* OUTPUT: 20101215 20101105 +0000040 20110218 20100913 +0000158 20110127 20110305 -0000037
Add/subtract days, months, and years from a date fields
You can use the following date arithmetic functions:
- ADDDAYS, ADDMONS and ADDYEARS can be used to add days, months or years to a date field.
- SUBDAYS, SUBMONS and SUBYEARS can be used to subtract days, months or years from a date field.
- DATEDIFF can be used to calculate the number of days between two date fields.
- NEXTDday can be used to calculate the next specified day of the week for a date field (where day can be SUN, MON, TUE, WED, THU, FRI or SAT). NEXTDFRI can be used to decide the next Friday for a C’ccyyddd’ date as a C’ccyy.ddd’ date:
- PREVDday can be used to calculate the previous specified day of the week for a date field (where day can be SUN, MON, TUE, WED, THU, FRI or SAT).
- LASTDAYW, LASTDAYM, LASTDAYQ, and LASTDAYY can be used to calculate the last day of the week, month, quarter or year for a date field.
Date Functions Example
//STEP0100 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD * 20101215 20110110 20110225 //SORTOUT DD SYSOUT=* //SYSIN DD * SORT FIELDS=COPY INREC OVERLAY=(15:1,8,Y4T,ADDDAYS,+15,TOGREG=Y4T(-), 30:1,8,Y4T,SUBDAYS,+23,TOGREG=Y4T(-)) /*
This job adds 15 days to a ‘yyyymmdd’ date in input positions 1-8 and converts the result to a ‘yyyy-mm-dd’ date in output positions 15-24. Subtracts 23 days from a ‘yyyymmdd’ date in input positions 1-8 and converts the result to a ‘yyyy-mm-dd’ date in output positions 30-39.
OUTPUT: 20101215 2010-12-30 2010-11-22 20110110 2011-01-25 2010-12-18 20110225 2011-03-12 2011-02-02
Use the following to calculate the next Friday for a C’ccyyddd’ date as a C’ccyy.ddd’ date: 3,7,Y4T,NEXTDFRI,TOJUL=Y4T(.)
Use the following to calculate the previous Wednesday for a P’yyddd’ date as a C’ccyymmdd’ date: 51,3,Y2U,PREVDWED,TOGREG=Y4T
Use the following to calculate the last day of the month for a C’mmddccyy’ date as a C’mmddccyy’ date: 28,8,Y4W,LASTDAYM,TOGREG=Y4W
This next job subtracts 3 months from a ‘yyddd’ date in input positions 1-5 and converts the result to a ‘dddyyyy’ date in output positions 11-17.
Sort by date, and calculate a specific day after and before a date, and the last day of the quarter for a date. The input date is in the form C’mmddyy’ and the output dates will be in the form ‘ddd-yyyy’.
INPUT:
010105
120699
021610
999999
092810
031500
000000
032505
110210
SYSIN:
OPTION Y2PAST=1990
SORT FIELDS=(1,6,Y2W,A)
OUTFIL REMOVECC, HEADER1=(1:'Input',15:'NEXTDFRI',25:'PREVDSUN',35:'LASTDAYQ'),
BUILD=(1:1,6,Y2W,TOJUL=Y4W(-),
15:1,6,Y2W,NEXTDFRI,TOJUL=Y4W(-),
25:1,6,Y2W,PREVDSUN,TOJUL=Y4W(-),
35:1,6,Y2W,LASTDAYQ,TOJUL=Y4W(-))
OUTPUT:
INPUT NEXTDFRI PREVDSUN LASTDAYQ
000-0000 000-0000 000-0000 000-0000
340-1999 075-2000 001-2005 084-2005
047-2010 271-2010 306-2010 999-9999
344-1999 339-1999 365-1999 077-2000
072-2000 091-2000 007-2005 361-2004
090-2005 091-2005 079-2005 090-2005
050-2010 045-2010 090-2010 274-2010
269-2010 273-2010 309-2010 304-2010
365-2010 999-9999 999-9999 999-9999
Note that the ‘000000’ and ‘999999’ input values are treated as special indicators for output.
Date Functions Example
//STEP0200 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD * 10036 11017 11122 //SORTOUT DD SYSOUT=* //SYSIN DD * OPTION Y2PAST=1980 SORT FIELDS=(1,5,Y2T,D) OUTREC BUILD=(1,5,5X,1,5,Y2T,SUBMONS,+3,TOJUL=Y4W) /* OUTPUT: 11122 0332011 11017 2902010 10036 3092009
Extract corresponding weekdays from dates
WEEKDAY function can be used to extract the day of the week from various types of dates. Three different output formats for the date are supported as follows:
- DIGIT1 – returns 1 digit for the weekday corresponding to the date (‘1’ for Sunday through ‘7’ for Saturday).
- CHAR3 – returns 3 characters for the weekday corresponding to the date (‘SUN’ for Sunday through ‘SAT’ for Saturday).
- CHAR9 – returns 9 characters for the weekday corresponding to the date (‘SUNDAY ‘ for Sunday through ‘SATURDAY ‘ for Saturday).
For example, if you use this job:
//S1 EXEC PGM=SORT //SYSOUT DD SYSOUT=* //SORTIN DD * 07132009 07152009 07172009 //SORTOUT DD SYSOUT=* //SYSIN DD * SORT FIELDS=COPY INREC OVERLAY=(15:1,8,Y4W,WEEKDAY=DIGIT1,X 1,8,Y4W,WEEKDAY=CHAR3,X, 1,8,Y4W,WEEKDAY=CHAR9) /* OUTPUT: 07132009 2 MON MONDAY 07152009 4 WED WEDNESDAY 07172009 6 FRI FRIDAY
If you wanted just the CHAR9 result, but with initial capitals, you could use these DFSORT control statements:
SYSIN: SORT FIELDS=COPY INREC OVERLAY=(15:1,8,Y4W,WEEKDAY=CHAR9, 16:16,8,TRAN=UTOL) OUTPUT: 07132009 Monday 07152009 Wednesday 07172009 Friday