Using QMF

Formatting dates and times

The date, time, and timestamp scalar functions change the data type of their arguments to the associated date/time data type.

The DATE function returns a date from a value. The argument must be a timestamp, a date, or a string representation of a date.

In the following SQL statement, the argument for DATE is a timestamp:

SELECT PROJNO, DATE(TIMESTAMP)
  FROM Q.PROJECT
  WHERE PROJNO = '1401'

The query produces this report:

+--------------------------------------------------------------------------------+
|   PROJNO  DATE                                                                 |
|   ------  ----------                                                           |
|   1401    1994-12-18                                                           |
+--------------------------------------------------------------------------------+

The TIME function returns a time from a value. The argument must be a time, a timestamp, or a string representation of a time. When you run the following SQL statement:

SELECT PRODNUM, TIME(TIMESTAMP)
  FROM Q.PROJECT
  WHERE YEAR(STARTD) = 1996

QMF produces this report, where TIME shows the time portions of three timestamps in the Q.PROJECT table:

+--------------------------------------------------------------------------------+
|   PRODNUM  TIME                                                                |
|   -------  --------                                                            |
|        10  10.14.44                                                            |
|        50  10.15.01                                                            |
|       150  10.22.23                                                            |
+--------------------------------------------------------------------------------+

The TIMESTAMP function returns a timestamp from a value or a pair of values. If only one argument is specified, it must be a timestamp, a string representation of a timestamp, a character string of length 8, or a character string of length 14. If the value is a character string of length 14, it must be in the form yyyymmddhhmmss, where yyyy is the year, mm is the month, dd is the day, hh is the hour, mm is the minute, and ss is the second.

If a second optional argument is specified, it must be a time or a string representation of a time, and the first argument must be a date or a string representation of a date. For example, for this statement:

TIMESTAMP (CURRENT DATE, '10.00.00')

QMF produces a timestamp that represents 10 a.m. today.

The CHAR function returns a string representation of a date/time value. CHAR changes the value of its argument (a date or time value) to the CHAR data type. The result of CHAR is a fixed-length character string representation of a date/time value in the format that is specified by its optional second argument. If the first argument is a date or a time, the second argument must be USA, ISO, JIS, EUR, or LOCAL. LOCAL refers to an installation default format. If you omit the second argument, the date or time format is ISO.

When you run the following SQL statement, with a second argument of USA for CHAR:

SELECT TEMPID, CHAR(INTDATE, USA)
  FROM Q.INTERVIEW
  WHERE MANAGER = 140

QMF produces this report:

+--------------------------------------------------------------------------------+
|           INTERVIEW                                                            |
|   TEMPID     DATE                                                              |
|   ------  ----------                                                           |
|      420  04/07/1990                                                           |
|      490  09/30/1990                                                           |
+--------------------------------------------------------------------------------+

Table 6 and Table 7 show examples of DATE and TIME formats in USA, ISO, JIS, and EUR. In these tables, USA refers to United States of America format, ISO refers to International Standards Organization format, JIS refers to Japanese Industrial Standard format, and EUR refers to European format.

Table 6. DATE formats

Table 7. TIME formats