HP 3000 Manuals HP 3000 Manuals
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
[REV BEG]
{{TO_DATE } }
{{TO_TIME } (StringExpression [,FormatSpecification])}
{{TO_DATETIME} }
{{TO_INTERVAL} }
{TO_CHAR (DateTimeExpression [,FormatSpecification]) }
{TO_INTEGER (DateTimeExpression ,FormatSpecification) }
[REV END]
Parameters--Conversion Functions
TO_DATE, TO_TIME, produce a result which is of the DATE, TIME,
TO_DATETIME, DATETIME, or INTERVAL type, respectively. Use
TO_INTERVAL 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.[REV BEG]
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.[REV END]
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.
[REV DEL]
SQL Syntax--FormatSpecification
{`FormatString' }
{:HostVariable [[INDICATOR]:IndicatorVariable]}
{? }
{:LocalVariable }
{:ProcedureParameter }
{::Built-inVariable }
Parameters--FormatSpecification
[REV BEG]
FormatString is a character string literal representing
the format of DateTimeExpression or
StringExpression.[REV END] It must be a string
literal, of maximum length 72 NATIVE-3000
characters. Format is composed of one or more
elements. Available format elements for the
date/time data types are described below. Only
NATIVE-3000 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.[REV BEG]
HostVariable identifies a host variable that contains the
format specification which determines how the
DateTimeExpression or StringExpression is to be
converted.[REV END]
IndicatorVariableames 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 the chapter "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.
[REV BEG]
* 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:[REV
END]
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
[REV BEG]
* 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)[REV END]
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.[REV BEG]
* 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'.[REV END]
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)
[REV BEG]
* The TO_INTERVAL function and the TO_CHAR function on INTERVAL
expressions use the interval default format 'DAYS
HH:MI:SS.FFF'.[REV END]
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'.
[REV BEG]
* 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 JCW HPSQLSPLITCENTURY to a value
between 0 and 100. If the YY part is less than the value of JCW
HPSQLSPLITCENTURY then the century part is set to 20, else it is
set to 19.[REV END]
* 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
1. 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.
[REV BEG]
The following statement inserts different date values depending on
the value of the JCW 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
[REV END]
2. 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'))
3. 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')
4. 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')