ALTDATE

The ALTDATE function returns the current date in the specified format or converts a user-specified date from one format to another.

The schema is DSN8.

The ALTDATE function returns the current date in one of the following formats or converts a user-specified date from one format to another:

   D MONTH YY    D MONTH YYYY    DD MONTH YY    DD MONTH YYYY
   D.M.YY        D.M.YYYY        DD.MM.YY       DD.MM.YYYY
   D-M-YY        D-M-YYYY        DD-MM-YY       DD-MM-YYYY
   D/M/YY        D/M/YYYY        DD/MM/YY       DD/MM/YYYY
   M/D/YY        M/D/YYYY        MM/DD/YY       MM/DD/YYYY
   YY/M/D        YYYY/M/D        YY/MM/DD       YYYY/MM/DD
   YY.M.D        YYYY.M.D        YY.MM.DD       YYYY.MM.DD
                 YYYY-M-D                       YYYY-MM-DD
                 YYYY-D-XX                      YYYY-DD-XX
                 YYYY-XX-D                      YYYY-XX-DD
   where:
   D:     Suppress leading zero if the day is less than 10
   DD:    Retain leading zero if the day is less than 10
   M:     Suppress leading zero if the month is less than 10
   MM:    Retain leading zero if the month is less than 10
   MONTH: Use English-language name of month
   XX:    Use a capital Roman numeral for month
   YY:    Use a year format without century
   YYYY:  Use a year format with century

The ALTDATE function demonstrates how you can create an overloaded function—a function name for which there are multiple function instances. Each instance supports a different parameter list enabling you to group related but distinct functions in a single user-defined function. The ALTDATE function has two forms.

Form 1: ALTDATE(output-format)

This form of the function converts the current date into the specified format.

output-format: A character string that matches one of the 34 date formats that are shown above. The character string must have a data type of VARCHAR and an actual length that is not greater than 13 bytes.

The result of the function is VARCHAR(17).

Form 2: ALTDATE(input-date, input-format, output-format)

This form of the function converts a date (input-date) in one user-specified format (input-format) into another format (output-format).

input-date: The argument must be a date or a character string representation of a date in the format specified by input-format. The character string must have a data type of VARCHAR and an actual length that is not greater than 17 bytes.
input-format: A character string that matches one of the 34 date formats that are shown above. The character string must have a data type of VARCHAR and an actual length that is not greater than 13 bytes.
output-format: A character string that matches one of the 34 date formats that are shown above. The character string must have a data type of VARCHAR and an actual length that is not greater than 13 bytes.

The result of the function is VARCHAR(17).

The following table shows the external and specific names for the two forms of the function, which are based on the input to the function.

Table 1. External program and specific names for ALTDATE
Conversion type	Input arguments	External name	Specific name
Current date	output-format (VARCHAR)	DSN8DUAD	DSN8.DSN8DUADV
User-specified date	input-date (VARCHAR) input-format (VARCHAR) output-format (VARCHAR)	DSN8DUCD	DSN8.DSN8DUCDVVV
User-specified date	input-date (DATE) input-format (VARCHAR) output-format (VARCHAR)	DSN8DUCD	DSN8.DSN8DUCDDVV

Example 1: Convert the current date into format 'DD MONTH YY', a format that will include any leading zero for the month, the name of the month in English, and the year without the two digits for the century.

   VALUES DSN8.ALTDATE( 'DD MONTH YY' );

Example 2: Convert the current date into format 'D.M.YYYY', a format that will suppress any leading zero for the day or month and include the year with the century.

   VALUES DSN8.ALTDATE( 'D.M.YYYY' );

Example 3: Convert the current date into format 'YYYY-XX-DD', a format that will include the century, the month of the year as a roman numeral, and the day of the month with any leading zero.

   VALUES DSN8.ALTDATE( 'YYYY-XX-DD' );

Example 4: Convert a date in the format of 'DD MONTH YYYY' to a date in the format of 'YYYY/MM/DD'.

    VALUES DSN8.ALTDATE( '11 November 1918',
                         'DD MONTH YYYY',
                         'YYYY/MM/DD' );

The result of the above example is '1918/11/18'.

Example 5: Convert the date that employee 000130 was hired, a date in ISO format, into the format of 'D.M.YY'.

   SELECT  FIRSTNME || ' '
        ||  LASTNAME || ' was hired on '
        ||  DSN8.ALTDATE( HIREDATE,
                         'YYYY-MM-DD',
                         'D.M.YY' )
      FROM  EMP
     WHERE  EMPNO  = '000130';

Assuming that the HIREDATE is '1971-07-28', the above example returns: '

DELORES QUINTANA
was hired on 28.7.71