Return to list of articles

This excerpt from the Spectrum Writer Reference Manual provides some tips that you may find helpful when working with date fields.

Working with Date Fields

Spectrum Writer supports over 30 different types of date fields commonly found in mainframe files. (For a list of these, see Appendix A, "Data Types" on page 609.) However, once a date field has been properly defined with the correct data type (in a FIELD statement), you no longer need to be concerned with how it was stored in the input file.

Internally, Spectrum Writer converts all date fields from their input file format into its own standard format. Thus, the conversion required to make various kinds of date fields and date literals compatible is done for you automatically. This means that, regardless of whether a date was stored as a Gregorian date, a Julian date or something else, you will always use date literals in the standard MM/DD/YYYY (or MM/DD/YY) format when testing their values. For example:

INCLUDEIF: JULIAN-START-DATE > 1/1/2001 AND GREGORIAN-END-DATE < 12/31/2001

Note: you can also write your date literals in DD/MM/YYYY and DD/MM/YY format, if you prefer. Just use the DDMMYYLIT option (in an OPTIONS statement).

Any date field can be compared with any other date field, regardless of how the two dates were stored in the input file. Spectrum Writer handles all necessary conversions:

INCLUDEIF: MY-JULIAN-DATE = MY-GREGORIAN-DATE

Century Windowing

Spectrum Writer stores all date fields internally with 4-digit years. If your input file contains date fields that do not have an explicit century (for example, YYMMDD or YYDDD dates), Spectrum Writer must decide what century to assign the date to. That is, it must decide whether YY means 19YY or 20YY. The CENTURY option (in an OPTIONS statement) is used to tell Spectrum Writer how to make this decision. Use it to specify a century cutoff year (from 0 to 99). For example:

OPTION: CENTURY(80)

The above statement tells Spectrum Writer that YY dates less than 80 are 20YY and all other dates are 19YY. Note that the CENTURY Option applies to all YY dates from all input files used in a run.

The CENTURY option also applies to the date literals in your Spectrum Writer control statements. Date literals may be written in either MM/DD/YYYY or MM/DD/YY format. However, all date literals are stored internally with 4-digit years. When you write a date literal in the MM/DD/YY format, Spectrum Writer assigns a century for you in the manner just described — based on the century cutoff year from the CENTURY option.

If you do not specify a CENTURY option, Spectrum Writer uses a default century cutoff year of 50. That means that, by default, all YY dates in a run fall in the range from 1950 to 2049. Of course, the century windowing logic applies only to YY date fields and literals. Date fields and literals that have a 4-digit year (YYYY) are not affected. Spectrum Writer uses the century contained in the YYYY value.

It is possible that different files in your shop will use different cutoff years. Since the CENTURY option applies to all YY dates in a run, it alone could not handle that situation. In such a case, use the CENTURY option for the most common cutoff year. Then use COMPUTE statements to perform custom century windowing logic on the non-standard cases. For example, assume that one file in your shop uses a cutoff year of 40, while the other files have cutoff years of 60. Specify 60 in your CENTURY parm to handle the most common cases. Then handle the exceptional case this way:

FIELD: YYMMDD-DATE COLUMN(1) LEN(6) TYPE(CHAR)
FIELD: YY-PART COLUMN(1) LEN(2) TYPE(CHAR)
COMPUTE: MY-DATE = WHEN(YY-PART < '40') ASSIGN(#MAKEDATE('20' + YYMMDD-DATE))
ELSE ASSIGN(#MAKEDATE('19' + YYMMDD-DATE))

You can store the COMPUTE statement right along with the FIELD statements in your file definition library.

How Dates Are Formatted in Your Reports

By default, all date fields, regardless of their century and regardless of how they are stored in the input file, are formatted in your reports like this: MM/DD/YY

Over 40 different date display formats are available if you want to format some or all of your date fields differently. The date display formats are listed in Appendix B, "Display Formats" (page 617). For example, you can specify the MM-DD-YYYY display format if you want a to display a date with a four-digit year.

COLUMNS: SALES-DATE(MM-DD-YYYY)

You can also change the default date display format for all dates in a report by using the FORMAT option (in an OPTIONS statement.) OPTIONS: FORMAT(YYYY-MM-DD)

Date Delimiters

Date display formats that contain a "dash" (–) result in dates formatted with a delimiter. (The delimiter appears where the dashes appear in the display format name.) By default, this delimiter is a "slash" (/). Thus, the MM-DD-YYYY display format results in dates like this:

12/31/2004

If you want a different delimiter for your displayed dates, use the DATEDELIM option (in an OPTIONS statement). For example: OPTIONS: DATEDELIM(’.’) The above statement would result in dates being formatted in your report like this (depending on the display format you choose):

12.31.04
31.12.04
04.12.31
12.31.2004, etc.

How Dates Are Formatted in Your PC Files

By default, dates in most PC files created by Spectrum Writer are in MM/DD/YY format. If you want MM/DD/YYYY dates in a PC File, use the FORMAT option (after the PC option) to specify a different default display format. For example:

OPTIONS: PC FORMAT(MM-DD-YYYY)

The FORMAT option changes the default display format for date fields. In the above example, dates will now be formatted as MM/DD/YYYY. This unquoted format works in most recent versions of the popular spreadsheet programs. If your PC program still requires quotation marks around dates, use this statement instead:

OPTIONS: PC FORMAT(Q-MM-DD-YYYY)

Note: Be sure that the FORMAT option follows the PC option. Otherwise, the PC option will reset the default date display format.

Working with Julian Dates

You may wonder if Julian date fields require different handling from other kinds of date fields. The answer is no. Once you have used the appropriate Julian DATATYPE in its FIELD statement, you (and other users of the file) can simply forget that the date was originally stored in Julian format. You will work with that date field in exactly the same way as you work with any other date field.

That means that even for fields stored in Julian format, you will still use date literals in the standard MM/DD/YYYY (or MM/DD/YY) format when making comparisons to them. (Spectrum Writer does not have a "YYDDD" format date literal, so do not try to use such a format.) Here is an example of comparing a Julian date with two date literals:

INCLUDEIF: MY-JULIAN-DATE > 1/1/2001 AND < 12/31/2001

You can also compare a Julian date field with another date field stored in a different format without any special effort on your part:

INCLUDEIF: MY-JULIAN-DATE = MY-GREGORIAN-DATE OR MY-SMF-DATE OR MY-STCK-DATE

The only exception to this is if your Julian date fields contain non-date values with special significance (perhaps all zeros, all nines, high-values, etc.) Since such values are not valid Julian dates, Spectrum Writer simply considers these values to be "invalid" data. (You would see ***I*** in your report for such cases.) It is possible to test for these special cases. But to do so, you will need to be aware of how the field is stored in the input record. You should compare the field to an explicit hexadecimal literal of the correct length. For example:

INCLUDEIF: MY-JULIAN-DATE <> X’F9F9F9F9F9’ /*COMPARE CHAR JULIAN DATE TO NINES */
INCLUDEIF: MY-JULIAN-PACKED-DATE <> X’000000’ /*COMPARE PACKED DATE TO LOW-VALUES */

As far as report output goes, by default Spectrum Writer formats Julian date field like all other date fields — in the standard MM/DD/YY format. So again, you don’t need to do anything special to have a Julian date field re-formatted into Gregorian in your report. Of course, you can also use an override display format to format a Julian date in any of the over 40 date formats available. (For a list, see "Date Display Formats" on page 620.)

Computing Dates Like "Yesterday," "Last Week", etc.

You can use Spectrum Writer’s powerful date-manipulation functions to compute dates or date ranges based on the system date. For example, to select all of the sales for "yesterday" from the SALES-FILE, we could use these statements:

COMPUTE: YESTERDAY = #INCDATE(–1, DAY)
INCLUDEIF: SALES-DATE = YESTERDAY

Similarly, to report on all sales made "last week," you could use these statements:

COMPUTE: START-DATE = #BEGWEEK(#INCDATE(–1, WEEK))
COMPUTE: END-DATE = #ENDWEEK(#INCDATE(–1, WEEK))
INCLUDEIF: SALES–DATE >= START-DATE AND <= END-DATE

You can also increment/decrement date and time pairs by units of time. For example, you could compute an "expiration date and time" that is 36 hours after the date and time of a sale this way:

COMPUTE: EXPIRE-DATE = #INCDATETIME(SALES-DATE, SALES-TIME, 36, HOURS)
COMPUTE: EXPIRE-TIME = #INCTIME(SALES-TIME, 36, HOURS)

You can find the complete syntax for all of these built-in functions, along with other date manipulation functions, in Appendix D, "Built-In Functions" on page 628.