Date/Time Functions

The following text describes the two types of date/time conversion functions:

The input functions convert character values into date/time values. With TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL you can enter date/time values in a format other than the default format.
The output functions convert date/time values out to integer or character values. With TO_CHAR you can specify an output format for a date/time column value other than the default format. With TO_INTEGER you can extract an element as an INTEGER value.

Date/time columns are displayed in the default format.

Scope

SQL Data Manipulation Statements

SQL Syntax—Conversion Functions

  { { TO_DATE
      TO_TIME
      TO_DATETIME
      TO_INTERVAL } (StringExpression [,FormatSpecification])
    TO_CHAR (DateTimeExpression [,FormatSpecification])
    TO_INTEGER (DateTimeExpression ,FormatSpecification)       }

Parameters—Conversion Functions

TO_DATE, TO_TIME, TO_DATETIME, TO_INTERVAL: produce a result which is of the DATE, TIME, DATETIME, or INTERVAL type, respectively. Use these functions in any expression.
TO_CHAR: produces the character string representation of the value in the column named in the first parameter in the format specified in the second parameter. The result type is VARCHAR with the length as specified by the format specification. If a format is not specified, the default format for the data type (and length) is used. Use this output function in any expression.
TO_INTEGER: produces an INTEGER value which represents a portion of the date/time column. The format specification is not optional in this case, and must consist of a single element (of the format specification). Use this output function in any expression.
StringExpression: is a string expression. Refer to the "Expression" section in this chapter for details on the syntax. The expression must be a CHAR or VARCHAR data type.
DateTimeExpression: is a Date/Time expression. See the "Expression" section of this chapter for more details on the syntax. The expression must be a DATE, TIME, DATETIME, or INTERVAL data type.
FormatSpecification: specifies the format of ColumnName or CharacterValue. Refer to the syntax for FormatSpecification later in this section. Format elements are presented in the "Description" section below.

SQL Syntax—FormatSpecification

  {'FormatString'
    :HostVariable [[INDICATOR]:IndicatorVariable]
    ?
    :LocalVariable
    :ProcedureParameter
    ::Built-inVariable                             }

Parameters—FormatSpecification

FormatString

is a character string literal representing the format of DateTimeExpression or StringExpression. It must be a string literal, of maximum length 72 characters. Format is composed of one or more elements. Available format elements for the date/time data types are described below. Only n-computer characters are allowed in the FormatString. The syntax for the format string follows:

{ FormatElement {Punctuation or Blank} [...] }

The format elements are listed in the "Description" section.

HostVariable

identifies a host variable that contains the format specification which determines how the DateTimeExpression or StringExpression is to be converted.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0: the value is not NULL
< 0: the value is NULL (The value in the host variable will be ignored.)

?

is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time.

LocalVariable

contains a value in a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

::Built-inVariable

is one of the following built-in variables used for error handling:

::sqlcode
::sqlerrd2
::sqlwarn0
::sqlwarn1
::sqlwarn2
::sqlwarn6
::activexact

The first six of these have the same meaning that they have as fields in the SQLCA in application programs. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to Chapter 4 “Constraints, Procedures, and Rules”

Description

If the format specification is optional and it is not supplied, the proper default format is used. If a date/time column or string literal appears in an expression without a conversion function, it is changed, if necessary, to the default format.
Date format is used by the TO_DATE function and by the TO_CHAR function on DATE expressions. The default format is 'YYYY-MM-DD'.
Listed here are format elements made up of numeric characters (digits 0 through 9):
CC
Century (00 to 99)
YYYY
Year (0000 to 9999)
YY
Year of century (00 to 99)
ZYY
YY with leading zeroes suppressed (0 to 99) (TO_CHAR only)
Q
Quarter (1 to 4) (TO_CHAR only )
MM
Month (01 to 12)
ZMM
MM with leading zeroes suppressed (1 to 12) (TO_CHAR only)
DAYS
Days since January 1, 0000 (0000000 to 3652436)
ZDAYS
DAYS with leading zeroes suppressed (0 to 3652436) (TO_CHAR only)
DDD
Day of year (001 to 366)
ZDDD
DDD with leading zeroes suppressed (1 to 366) (TO_CHAR only)
DD
Day of month (01 to 31)
ZDD
DD with leading zeroes suppressed (1 to 31) (TO_CHAR only)
D
Day of week (1 to 7) (TO_CHAR only)
The Z prefix and Q and D are only allowed for the function TO_CHAR. If YY is used without CC, the default CC is 19. The following elements are for representing alphabetic characters:
MONTH
Name of month
MON
Abbreviated name of month
DAYOFWEEK
Name of day
DAY
Abbreviated name of day
-/:.,
Punctuation marks reproduced in value (includes spaces)
"string"
Quoted string reproduced in value
Delimiting punctuation marks must be the same in the value parameter and the format specification parameter.
Capitalization in alphabetic representations follows the capitalization of the corresponding format element. Elements may be represented in uppercase, lowercase, or initial caps. Other mixtures of uppercase and lowercase letters result in an error. For example:
'DAYOFWEEK' —--> MONDAY 'Dayofweek' —--> Monday 'dayofweek' —--> monday 'dAyOfWeEk' —--> error condition
Time format is used by the TO_TIME function and by the TO_CHAR functions on TIME expressions. The default format is 'HH:MI:SS'.
Listed here are formats for elements made up of numeric characters:
HH or HH24
Hour of day (00 to 23)
ZHH or ZHH24
HH or HH24 with leading zeroes suppressed (0 to 23) (TO_CHAR only)
HH12
Hour of day (00 to 12)
ZHH12
HH12 with leading zeroes suppressed (0 to 12) (TO_CHAR only)
MI
Minute (00 to 59)
ZMI
MI with leading zeroes suppressed (0 to 59) (TO_CHAR only)
SS
Second (00 to 59)
ZSS
SS with leading zeroes suppressed (0 to 59) (TO_CHAR only)
SECONDS
Seconds past midnight (00000 to 86399)
ZSECONDS
SECONDS with leading zeroes suppressed (0 to 86399) (TO_CHAR only)
Z is not allowed for the input functions. The following elements are for representing alphabetic characters:
AM or PM
AM/PM indicator (use capital letters)
A.M. or P.M.
A.M./P.M. indicator with periods (use capital letters)
./:.,
Punctuation marks reproduced in value (includes spaces)
"string"
Quoted string reproduced in value
Delimiting punctuation marks must be the same in the value parameter and the format specification parameter.
The TO_DATETIME function and the TO_CHAR function on TIME expressions use the date/time default format 'YYYY-MM-DD HH:MI:SS.FFF'.
In addition to all formats shown for the date and time format specifications above, the following are also allowed for date/time formats (made up of the numeric characters 0 through 9):
F
Tenth of a second (.0 to .9)
FF
Hundredth of a second (.00 to .99)
FFF
Thousandth of a second (.000 to .999)

The TO_INTERVAL function and the TO_CHAR function on INTERVAL expressions use the interval default format 'DAYS HH:MI:SS.FFF'.

The following formats are allowed in an interval format specification:

   DAYS             MI            SECONDS           FFF
   ZDAYS            ZMI           ZSECONDS          -/:.,
   HH or HH24       SS            F                 "string"
   ZHH or ZHH24     ZSS           FF

These were described in the TIME and DATETIME format specifications above.

Literals for date/time data types which do not specify all elements of the date/time value are expanded and filled as described below:
- INTERVAL is zero filled on the left and the right.
- DATE, TIME, and DATETIME are left-filled with the current values from the system clock, and right-filled with appropriate portions of the default '0000-01-01 00:00:00.000'.
When YY is specified in the FormatSpecification and if its value in StringExpression is less than 50, then the century part of DATE and DATETIME defaults to 20, else it is set to 19. This behavior can be overridden by setting the environment variable HPSQLsplitcentury to a value between 0 and 100. If the YY part is less than the value of environment variable HPSQLsplitcentury then the century part is set to 20, else it is set to 19.
Output values are truncated, not rounded, to fit in the specified format.

The TO_INTEGER format specification is not optional, and must consist of one of the following single elements only:

   CC         MM        DAYS        SS
   YYYY       DDD       HH or HH24  SECONDS
   YY         DD        HH12        F, FF, or FFF
   Q          D         MI

ADD_MONTHS is a related function. ADD_MONTHS adds a duration of months to a DATE or DATETIME column. Refer to the Add Months Function for further information.

Examples

Date format
In the example below, the format MM/DD/YY is used to enter a date instead of using the default format, which is YYYY-MM-DD:
```
 INSERT INTO ManufDB.TestData(batchstamp, testdate)
     VALUES (TO_DATETIME ('07/02/89 03:20.000', 'MM/DD/YY HH12:MI.FFF'),
              TO_DATE('10/02/84','MM/DD/YY'))
```
To return the date entered in the above example, in a format other than the default format, the desired format is specified in the second parameter of the TO_CHAR conversion function:
```
   SELECT TO_CHAR(testdate, 'Dayofweek, Month DD')
       FROM ManufDB.TestData
    WHERE labtime < '0 05:00:00.000'
```
The value "Friday, July 13" is selected from TestData.
The following statement inserts different date values depending on the value of the environment variable HPSQLsplitcentury, if it is set.
```
    INSERT INTO ManufDB.TestData(testdata)
            VALUES (TO_DATE ('30/10','YY/MM'))
```
Case 1: HPSQLsplitcentury is not set; inserts 2030-10-01
Case 2: HPSQLsplitcentury is set to 0; inserts 1930-10-01
Case 3: HPSQLsplitcentury is set to 70; inserts 2030-10-01

Time format

   INSERT INTO ManufDB.TestData(teststart, batchstamp)
        VALUES (TO_TIME('01:53 a.m.','HH12:MI a.m.'),
                TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM'))

Datetime format

 UPDATE ManufDB.TestData
     SET batchstamp = TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM')
   WHERE batchstamp = TO_DATETIME('11.01.84 1.11 PM',  'DD.MM.YY HH12.MI PM')

Interval format

   UPDATE ManufDB.TestData
      SET labtime = TO_INTERVAL('06 10:12:11.111', 'DAYS HH:MI:SS.FFF')
    WHERE testdate = TO_DATE('10.02.84','MM.DD.YY')