Optimization of the HPART, DPART, HDIFF, HDATE, and DATEDIF Functions

In this section:

The relational adapters can translate certain calls to the HPART, DPART, HDIFF, HDATE, and DATEDIF functions to SQL date and time functions, allowing the optimization of some TABLE requests with DEFINE fields that call these functions.
Top of page
x
Optimization of the DATEDIF and HDIFF Functions

How to:

Reference:

The DATEDIF function returns the difference between two dates in units, and the HDIFF function returns the difference between two date-time values in units. Calls to these functions can be translated to SQL when the unit is the number of days between two values.

x
Syntax: How to Calculate the Number of Days Between Date or Date-Time Values

The syntax for the DATEDIF function with the day unit is

DATEDIF(from_date, to_date, 'D')

The syntax for the HDIFF function with the day unit is

HDIFF(to_date_time, from_date_time, 'DAY', outfield)

where:

from_date

Is the starting date or date-time value from which to calculate the difference.

to_date

Is the ending date or date-time value from which to calculate the difference.

outfield

Numeric

Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.

x
Reference: Conversion of the DATEDIF and HDIFF Functions to SQL

For Microsoft SQL Server, Oracle, SYBASE ASE and IQ, calls to DATEDIF and HDIFF translate calls to the native DATE-TIME functions with Datepart Parameter DAY, Startdate, and Enddate.

For example, for DB2, calls to DATEDIF and HDIFF translate as calls to the DAYS function:

SELECT DAYS(d2) - DAYS(d1) FROM tablename

For Teradata, calls to DATEDIF and HDIFF translate as a simple difference between two date values for the DATE datatype and as calls to the CAST function for the TIMESTAMP datatype.
Example: Optimizing the Difference Between DB2 Date or Timestamp Columns

The following Master File represents a DB2 table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:

FILENAME=DATETIME, SUFFIX=DB2     , $
  SEGMENT=DATETIME, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $

The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 DATE2 TIMEST1 TIMEST2 
WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND
HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1
END

The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL DAYS function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2"
  FROM OWNER1.DATETIME T1  
 WHERE ((DAYS(T1."TIMEST1") - DAYS(T1."TIMEST1")) = 1) AND              
       ((DAYS(T1."DATE1") - DAYS(T1."DATE2")) < 5)                      
       FOR FETCH ONLY;
Example: Optimizing the Difference Between Oracle Date or Timestamp Columns

The following Master File represents an Oracle table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:

FILENAME=DATETIME, SUFFIX=SQLORA  , $
  SEGMENT=DATETIME, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $

The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 DATE2 TIMEST1 TIMEST2 
WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND
HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1
END

The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL EXTRACT and TRUNC functions in the SQL WHERE predicate:

SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2"
   FROM OWNER1.DATETIME T1
   WHERE (EXTRACT(DAY FROM (TRUNC(T1."TIMEST1") - TRUNC(T1."TIMEST1")) 
   DAY(9) TO SECOND) = 1)
   AND (EXTRACT(DAY FROM (TRUNC(T1."DATE1") - TRUNC(T1."DATE2"))
   DAY(9) TO SECOND) < 5);
Top of page
x
Optimization of the HPART and DPART Functions

How to:

Reference:

The HPART function extracts a specified component from a date-time value and returns it in numeric format. The DPART function extracts a specified component from a date value and returns it in numeric format. Calls to HPART and DPART are optimized when the second parameter is a case insensitive YEAR, MONTH, QUARTER, DAY-OF-MONTH, or DAY. Calls to HPART are also optimized when the second argument is a case-insensitive HOUR, MINUTE, SECOND, or MICROSECOND.
x
Syntax: How to Extract a Component From a Date-Time Value
HPART(dtvalue, 'component', outfield)

where:

dtvalue

Date-time

Is a date-time value, the name of a date-time field that contains the value, or an expression that returns the value.

component

Alphanumeric

Is the name of the component to be retrieved enclosed in single quotation marks.

outfield

Integer

Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.

x
Syntax: How to Extract a Date Component and Return a Date Component in Integer Format
DPART (datevalue, 'component', outfield)

where:

datevalue

Date

Is a full component date.

component

Alphanumeric

Is the name of the component to be retrieved enclosed in single quotation marks. Valid values are:

For year: YEAR, YY

For month: MONTH, MM

For day: DAY, For day of month: DAY-OF-MONTH.

For quarter: QUARTER, QQ

outfield

Integer

Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.
x
Reference: Conversion of the HPART Function to SQL

For Microsoft SQL Server, SYBASE ASE, and IQ, calls to HPART and DPART translate as calls to the DATEPART function.

For DB2 and MySQL, calls to HPART translate as calls to the YEAR, MONTH, or DAY function.

For Oracle and TERADATA (Both DATE and TIMESTAMP datatypes), calls to HPART and DPART translate as calls to the EXTRACT function.
Example: Extracting a Component From a DB2 Timestamp Column

The following request uses the HPART function to retrieve the year component from the DB2 TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT TIMEST1
WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008
END

The generated SQL replaces the call to the HPART function with a call to the SQL YEAR function in the SQL WHERE predicate:

SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1                 
   WHERE (YEAR(T1."TIMEST1") = 2008)                          
    FOR FETCH ONLY;
Example: Extracting a Component From an Oracle Timestamp Column

The following request uses the HPART function to retrieve the year component from the Oracle TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT TIMEST1
WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008
END

The generated SQL replaces the call to the HPART function with a call to the SQL EXTRACT function in the SQL WHERE predicate:

SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1    
    WHERE (EXTRACT(YEAR FROM T1."TIMEST1") = 2008);
Top of page
x
Optimization of the HDATE Function

How to:

Reference:

The HDATE function converts the date portion of a date-time value to the date format YYMD.

x
Syntax: How to Extract the Date Portion of a Date-Time Value
HDATE(dtvalue, {'YYMD'|outfield})

where:

dtvalue

Date-time

Is the date-time value to be converted, the name of a date-time field that contains the value, or an expression that returns the value.

YYMD

Date

Is the output format. The value must be YYMD.

outfield

YYMD

Is the field that contains the result.

x
Reference: Conversion of the HDATE Function to SQL

For Microsoft SQL Server (2008 and up), SYBASE ASE and IQ, DB2, TERADATA, and MySQL, calls to DATE translate as calls to the CAST function.

For Oracle, calls to HDATE translate as calls to the TRUNC function.
Example: Extracting the Date Portion of a DB2 Timestamp Column

The following request uses the HDATE function to extract the date portion of the TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 TIMEST1 
WHERE HDATE(TIMEST1, 'YYMD') GT DATE1
END

In the generated SQL, the call to HDATE is replaced by a call to the SQL CAST function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1     
   WHERE (CAST(T1."TIMEST1" AS DATE) > T1."DATE1")                        
   FOR FETCH ONLY;
Example: Extracting the Date Portion of an Oracle Timestamp Column

The following request uses the HDATE function to extract the date portion of the TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 TIMEST1 
WHERE HDATE(TIMEST1, 'YYMD') GT DATE1
END

In the generated SQL, the call to HDATE is replaced by a call to the SQL TRUNC function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1     
    WHERE (TRUNC(T1."TIMEST1") > T1."DATE1");
Information Builders