Using Standard Date Functions

In this section:

When using standard date functions, you need to understand the settings that alter the behavior of these functions, as well as the acceptable formats and how to supply values in these formats.

You can affect the behavior of date functions in the following ways:

For detailed information on each standard date function, see:

DATEADD: Adding or Subtracting a Date Unit to or From a Date

DATECVT: Converting the Format of a Date

DATEDIF: Finding the Difference Between Two Dates

DATEMOV: Moving a Date to a Significant Point

DATETRAN: Formatting Dates in International Formats

DPART: Extracting a Component From a Date

FIYR: Obtaining the Financial Year

FIQTR: Obtaining the Financial Quarter

FIYYQ: Converting a Calendar Date to a Financial Date

TODAY: Returning the Current Date

Top of page
x
Specifying Work Days

In this section:

You can determine which days are work days and which are not. Work days affect the DATEADD, DATEDIF, and DATEMOV functions. You identify work days as business days or holidays.

x
Specifying Business Days

Business days are traditionally Monday through Friday, but not every business has this schedule. For example, if your company does business on Sunday, Tuesday, Wednesday, Friday, and Saturday, you can tailor business day units to reflect that schedule.

x
Syntax: How to Set Business Days
SET BUSDAYS = smtwtfs

where:

smtwtfs

Is the seven character list of days that represents your business week. The list has a position for each day from Sunday to Saturday:

  • To identify a day of the week as a business day, enter the first letter of that day in that day's position.
  • To identify a non-business day, enter an underscore (_) in that day's position.

If a letter is not in its correct position, or if you replace a letter with a character other than an underscore, you receive an error message.
Example: Setting Business Days to Reflect Your Work Week

The following designates work days as Sunday, Tuesday, Wednesday, Friday, and Saturday: 

SET BUSDAYS = S_TW_FS
x
Syntax: How to View the Current Setting of Business Days
? SET BUSDAYS
x
Specifying Holidays

You can specify a list of dates that are designated as holidays in your company. These dates are excluded when using functions that perform calculations based on working days. For example, if Thursday in a given week is designated as a holiday, the next working day after Wednesday is Friday.

To define a list of holidays, you must:

  1. Create a holiday file using a standard text editor.
  2. Select the holiday file by issuing the SET command with the HDAY parameter.
x
Reference: Rules for Creating a Holiday File
  • Dates must be in YYMD format.
  • Dates must be in ascending order.
  • Each date must be on its own line.
  • Each year for which data exists must be included. Calling a date function with a date value outside the range of the holiday file returns a zero for business day requests.
  • You may include an optional description of the holiday, separated from the date by a space.
x
Procedure: How to Create a Holiday File
  1. In a text editor, create a list of dates designated as holidays using the Rules for Creating a Holiday File.
  2. Save the file:

    The file must be a member of ERRORS named HDAYxxxx.

    where:

    xxxx

    Is a string of text four characters long.
x
Syntax: How to Select a Holiday File
SET HDAY = xxxx

where:

xxxx

Is the part of the name of the holiday file after HDAY. This string must be four characters long.
Example: Creating and Selecting a Holiday File

The following is the HDAYTEST file, which establishes holidays: 

19910325 TEST HOLIDAY
19911225 CHRISTMAS

This request uses HDAYTEST in its calculations:

SET BUSDAYS = SMTWTFS
SET HDAY = TEST
TABLE FILE MOVIES
PRINT TITLE RELDATE
COMPUTE NEXTDATE/YMD = DATEADD(RELDATE, 'BD', 1);
WHERE RELDATE GE '19910101';
END
Top of page
x
Enabling Leading Zeros For Date and Time Functions in Dialogue Manager

How to:

If you use a date and time function in Dialogue Manager that returns a numeric integer format, Dialogue Manager truncates any leading zeros. For example, if a function returns the value 000101 (indicating January 1, 2000), Dialogue Manager truncates the leading zeros, producing 101, an incorrect date. To avoid this problem, use the LEADZERO parameter.

LEADZERO only supports an expression that makes a direct call to a function. An expression that has nesting or another mathematical function always truncates leading zeros. For example,

-SET &OUT = AYM(&IN, 1, 'I4')/100;

truncates leading zeros regardless of the LEADZERO parameter setting.

x
Syntax: How to Set the Display of Leading Zeros
SET LEADZERO = {ON|OFF}

where:

ON

Displays leading zeros if present.

OFF

Truncates leading zeros. OFF is the default value.
Example: Displaying Leading Zeros

The AYM function adds one month to the input date of December 1999: 

-SET &IN = '9912';
-RUN
-SET &OUT = AYM(&IN, 1, 'I4');
-TYPE &OUT

Using the default LEADZERO setting, this yields:

1

This represents the date January 2000 incorrectly. Setting the LEADZERO parameter in the request as follows:

SET LEADZERO = ON
-SET &IN = '9912';
-SET &OUT = AYM(&IN, 1, 'I4');
-TYPE &OUT

results in the following:

0001

This correctly indicates January 2000.

Information Builders