Date Built-In Functions

Return to "Working with Dates".

Built-In Functions

A number of built–in functions are available for use within computational expressions. Computational expressions are used in COMPUTE statements. These built–in functions are listed on the following pages, according to the type of data returned by the function (character, numeric, date, time or boolean).

The arguments to a function will not necessarily be of the same data type as the result. The data type expected for each argument is indicated in a function’s syntax. For example, "char" means that a character argument is expected.

Except where otherwise indicated, an argument may be any of the following:
a literal value
the name of a field from any input file
the name of a computed field (from any previous COMPUTE statement)
a computational expression (which may itself involve other built–in functions)

Separate the arguments with blanks and/or commas.

Following is a list of Spectrum Writer's built–in functions related to date processing.

Date Built-In Functions

#BEGMONTH[(date)]

Returns the first day of the month in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #BEGMONTH(5/15/2007) results in A=5/1/2007

#BEGWEEK[(date)]

Returns the Sunday of the calendar week in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #BEGWEEK(5/15/2007) results in A=5/13/2007

Note: You can also use this function to return any particular day of a given week (Monday, Tuesday, etc.). Just use it in combination with an #INCDATE function that adds the appropriate number of days to the result . Add 1 to get Monday, 2 to get Tuesday, and so on. The following example returns the Wednesday of the week that SALES-DATE falls within.

Example: WED-SALES-DATE = #INCDATE(#BEGWEEK(SALES-DATE), 3, DAYS)

#BEGYEAR[(date)]

Returns the first day of the year in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #BEGYEAR(5/15/2007) results in A=1/1/2007

#ENDMONTH[(date)]

Returns the last day of the month in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #ENDMONTH(5/15/2007) results in A=5/31/2007

#ENDWEEK[(date)]

Returns the Saturday of the calendar week in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #ENDWEEK(5/15/2007) results in A=5/19/2007

#ENDYEAR[(date)]

Returns the last day of the year in which the date argument occurs. _{Notes 2, 3}

Example: COMPUTE: A = #ENDYEAR(5/15/2007) results in A=12/31/2007

#INCDATE([date,] number, units)

Returns the date obtained by incrementing the argument date by the given number of units. Units can be any of these keywords or abbreviations: _{Notes 2, 3}

DAYS, DAY, D
WEEKS, WEEK, WKS, WK, W MONTHS, MONTH, MONS, MON, M
YEARS, YEAR, YRS, YR, Y

Example:
COMPUTE: A = #INCDATE(5/15/2007, 3, WEEKS) results in A = 6/5/2007
COMPUTE: YESTERDAY = #INCDATE( -1, DAYS) results in YESTERDAY being the date before the system date.

Note: When incrementing by months or years, the day portion of the resulting date is sometimes changed to the last day of the month, in order to return a valid calendar date.

COMPUTE: A = #INCDATE(5/31/2007, 1, MONTH) results in A = 6/30/2007 (not 6/31/2007 which is not a valid date)
COMPUTE: B = #INCDATE(2/29/2008, 1, YEAR) results in B = 2/28/2009 (not 2/29/2009 which is not a valid date)

#INCDATETIME([date,] [time,] number, units) or
#INCDATETIME([date,] [time,] time)

Returns the date obtained by incrementing the date and time arguments by the given number of units, or by a time value. Units can be any of these keywords or abbreviations: _{Notes 1, 2, 3}

SECONDS, SECOND, SECS, SEC, S
MINUTES, MINUTE, MINS, MIN, M
HOURS, HOUR, HRS, HR, H

Examples:
COMPUTE: A= #INCDATETIME(1/1/2008, 23:45:00, 12, MINUTES) results in A = 1/1/2008
COMPUTE: B= #INCDATETIME(1/1/2008, 23:45:00, 16, MINUTES) results in A = 1/2/2008

Note: This function is often used in conjunction with #INCTIME. Together, they let you add a time interval to a starting date and time and get the resulting date and time. For example, to compute an "expiration" date and time that is 12 hours after SALESDATE and SALES-TIME, you could use the following:

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

Note: Transitions to or from Daylight Savings Time are not taken into account by this function.

#MAKEDATE(char/num)

For character arguments, converts the YYMMDD or YYYYMMDD character string into the corresponding date. The character argument must be either 6 or 8 bytes in length. When a YYMMDD argument is used, Spectrum Writer assigns the century based on the CENTURY option in effect (page 559):

Example: COMPUTE: A = #MAKEDATE('20070331') results in A=3/31/2007

For numeric arguments, the argument is treated as a "day in century" value. The numeric argument must between be 1 (corresponding to January 1, 1900) and 73,049 (corresponding to December 31, 2099) inclusive. The function returns the date corresponding to the numeric day from the start of the 20th century. (Use this function to change the results of the #MAKENUM(date) function back into a date.)

Example: COMPUTE: A = #MAKEDATE(39446) results in A=12/31/2007

#YMD(num, num, num)
#MDY(num, num, num)
#DMY(num, num, num)

Returns a date value based on the three numeric arguments (representing month, day and year in the order indicated by the function name.) The resulting date is not validity-checked to see if it is an actual calendar date. (You can use the #REALDATE function to find out.) The numeric argument representing the year can be any 1 to 4 digit number, and the month and day arguments can be any 1 or 2 digit number.

Example:
COMPUTE: A = #MDY(12,31,2007) results in A=12/31/2007
COMPUTE: B = #YMD(9999,99,99) results in B=99/99/9999 F

Function Notes:
1. If the time argument is omitted, the system time is used.
2. If the date argument is omitted, the system date is used.
3. If the date argument is not a valid calendar date, the function returns an "invalid data" error (***I***).

Note: You can use #REALDATE to determine whether a given date is valid or not.

Example:

COMPUTE X-IS-REALDATE = #REALDATE(X)
COMPUTE: BEGIN-X-MONTH =
WHEN(X-IS-REALDATE) ASSIGN(#BEGMONTH(X))
ELSE ASSIGN(99/99/9999)

The above example results in BEGIN-X-MONTH being the first day of the month in which X occurs if X is a valid calendar date. Otherwise, BEGIN-XMONTH will contain 99/99/9999.

Return to "Working with Dates".