|
|
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.
SQL Data Manipulation Statements
{ { TO_DATE
TO_TIME
TO_DATETIME
TO_INTERVAL } (StringExpression [,FormatSpecification])
TO_CHAR (DateTimeExpression [,FormatSpecification])
TO_INTEGER (DateTimeExpression ,FormatSpecification)
}
- 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.
{'FormatString'
:HostVariable [[INDICATOR]:IndicatorVariable]
?
:LocalVariable
:ProcedureParameter
::Built-inVariable
}
- 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"
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.
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')
|