Simplified Date and Date-Time Functions

In this section:

New date and date-time functions have been developed that make it easier to understand and enter the required arguments. These functions have streamlined parameter lists, similar to those used by SQL functions. In some cases, these simplified functions provide slightly different functionality than previous versions of similar functions.

The simplified functions do not have an output argument. Each function returns a value that has a specific data type.

When used in a request against a relational data source, these functions are optimized (passed to the RDBMS for processing).

Standard date and date-time formats refer to YYMD and HYYMD syntax (dates that are not stored in alphanumeric or numeric fields). Dates not in these formats must be converted before they can be used in the simplified functions. Literal date-time values can be used with the DT function.

All arguments can be either literals, field names, or amper variables.


Top of page

x
DTADD: Incrementing a Date or Date-Time Component

How to:

Reference:

Given a date in standard date or date-time format, DTADD returns a new date after adding the specified number of a supported component. The returned date format is the same as the input date format.



x
Syntax: How to Increment a Date or Date-Time Component
DTADD(date, component, increment)

where:

date

Date or date-time

Is the date or date-time value to be incremented.

component

Keyword

Is the component to be incremented. Valid components (and acceptable values) are:

  • YEAR (1-9999)
  • QUARTER (1-4)
  • MONTH (1-12)
  • WEEK (1-53). This is affected by the WEEKFIRST setting.
  • DAY (of the Month, 1-31)
  • HOUR (0-23)
  • MINUTE (0-59)
  • SECOND (0-59)
increment

Integer

Is the value (positive or negative) to add to the component.



Example: Incrementing the DAY Component of a Date

The following request against the WF_RETAIL data source adds three days to the employee date of birth:

DEFINE FILE WF_RETAIL
NEWDATE/YYMD = DTADD(DATE_OF_BIRTH, DAY, 3);
MGR/A3 = DIGITS(ID_MANAGER, 3);
END
TABLE FILE WF_RETAIL
SUM MGR NOPRINT DATE_OF_BIRTH NEWDATE
BY MGR
ON TABLE SET PAGE NOPAGE
END

The output is:



x
Reference: Usage Notes for DTADD

Top of page

x
DTDIFF: Returning the Number of Component Boundaries Between Date or Date-Time Values

How to:

Given two dates in standard date or date-time formats, DTDIFF returns the number of given component boundaries between the two dates. The returned value has integer format for calendar components or double precision floating point format for time components.



x
Syntax: How to Return the Number of Component Boundaries
DTDIFF(end_date, start_date, component)

where:

end_date

Date or date-time

Is the ending date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

start_date

Date or date-time

Is the starting date in either standard date or date-time format. If this date is given in standard date format, all time components are assumed to be zero.

component

Keyword

Is the component on which the number of boundaries is to be calculated. For example, QUARTER finds the difference in quarters between two dates. Valid components (and acceptable values) are:

  • YEAR (1-9999)
  • QUARTER (1-4)
  • MONTH (1-12)
  • WEEK (1-53). This is affected by the WEEKFIRST setting.
  • DAY (of the Month, 1-31)
  • HOUR (0-23)
  • MINUTE (0-59)
  • SECOND (0-59)


Example: Returning the Number of Years Between Two Dates

The following request against the WF_RETAIL data source calculates employee age when hired:

DEFINE FILE WF_RETAIL
YEARS/I9 = DTDIFF(START_DATE, DATE_OF_BIRTH, YEAR); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE DATE_OF_BIRTH YEARS AS 'Hire,Age'
BY  EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AA'
ON TABLE SET PAGE NOPAGE
END 

The output is:


Top of page

x
DTPART: Returning a Date or Date-Time Component in Integer Format

How to:

Given a date in standard date or date-time format and a component, DTPART returns the component value in integer format.



x
Syntax: How to Return a Date or Date-Time Component in Integer Format
DTPART(date, component)

where:

date

Date or date-time

Is the date in standard date or date-time format.

component

Keyword

Is the component to extract in integer format. Valid components (and values) are:

  • YEAR (1-9999).
  • QUARTER (1-4).
  • MONTH (1-12).
  • WEEK (of the year, 1-53). This is affected by the WEEKFIRST setting.
  • DAY (of the Month, 1-31).
  • DAY_OF_YEAR (1-366).
  • WEEKDAY (day of the week, 1-7). This is affected by the WEEKFIRST setting.
  • HOUR (0-23).
  • MINUTE (0-59).
  • SECOND (0-59.)
  • MILLISECOND (0-999).
  • MICROSECOND (0-999999).


Example: Extracting the Quarter Component as an Integer

The following request against the WF_RETAIL data source extracts the QUARTER component from the employee start date:

DEFINE FILE WF_RETAIL
QTR/I2 =DTPART(START_DATE, QUARTER); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE QTR AS Quarter
BY  EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AH'
ON TABLE SET PAGE NOPAGE
END 

The output is:


Top of page

x
DTRUNC: Returning the Start of a Date Period for a Given Date

How to:

Given a date or timestamp and a component, DTRUNC returns the first date within the period specified by that component.



x
Syntax: How to Return the First Date of a Date Period
DTRUNC(date_or_timestamp, date_period)

where:

date_or_timestamp

Date or date-time

Is the date or timestamp of interest.

date_period

Is the period whose starting date you want to find. Can be one of the following:

  • DAY, returns day of the month (1-31).
  • YEAR, returns year (1-9999).
  • MONTH, returns month (1-12).
  • QUARTER, returns quarter (1-4).


Example: Returning the First Date in a Date Period

In the following request against the WF_RETAIL data source, DTRUNC returns the first date of the quarter given the start date of the employee:

DEFINE FILE WF_RETAIL
QTRSTART/YYMD = DTRUNC(START_DATE, QUARTER); 
END
TABLE FILE WF_RETAIL
PRINT START_DATE QTRSTART AS 'Start,of Quarter'
BY EMPLOYEE_NUMBER 
WHERE EMPLOYEE_NUMBER CONTAINS 'AH'
ON TABLE SET PAGE NOPAGE
END 

The output is:


iWay Software