HP 3000 Manuals HP 3000 Manuals
Using Date/Time Input Functions
The new input functions are designed so that you can easily input data
for a given date/time data type in either the default format or a format
of your choice. (When you do not include a format specification, the
default is used.)
You have the option of choosing a literal string or a host variable to
indicate a desired data value and/or optional format specification. See
the ALLBASE/SQL Reference Manual for detailed syntax.
Following is the general syntax for date/time input functions:
{TO_DATETIME (DataValue [,FormatSpecification])}
{TO_DATE (DataValue [,FormatSpecification]) }
{TO_TIME (DataValue [,FormatSpecification]) }
{TO_INTERVAL (DataValue [,FormatSpecification])}
Input functions can be used in DML operations as shown in Table 11-1 .
It is most appropriate to use date/time input functions in a WHERE,
VALUES, or SET clause. Although they can be used in a select list, it is
generally not appropriate to do so. The data value returned to the
function in this instance is not a column value but is identical to the
value you specify as input to the function.
Examples of TO_DATETIME, TO_DATE, TO_TIME, and TO_INTERVAL Functions
Imagine a situation in which users will be inputting and retrieving
date/time data in formats other than the default formats. (Refer to the
ALLBASE/SQL Reference Manual for default format specifications.)
The data is located in the TestData table in the manufacturing database.
(Reference appendix C in the ALLBASE/SQL Reference Manual .)
You are to provide them with the capability of keying and retrieving data
in the formats shown in Table 11-3 .
Table 11-3. Sample of User Requested Formats for Date/Time Data
----------------------------------------------------------------------------------------------
| | | |
| Date/Time Data Type | Desired Format Specification | Length of Format |
| | | Specification in ASCII |
| | | Characters |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATETIME | MM-DD-YYYY HH:MM:SS.FFF | 23 |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| DATE | MM-DD-YYYY | 10 |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| TIME | HH:MM:SS 1 | 8 |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| INTERVAL | DDDDDDD HH:MM:SS | 16 |
| | | |
----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
| |
| 1 This is the default time data format. |
| |
------------------------------------------------------------------------------------------
You might use the following generic code examples to meet their needs.
Example Using the INSERT Command.
Your application allows users to enter data in their desired formats with
a minimum of effort on your part.
BEGIN DECLARE SECTION
Declare input host variables (:BatchStamp, :BatchStamp-Format, :TestDate,
:TestDate-Format, :TestStart, :LabTime, and LabTime-Format) to be compatible
with data type CHAR or VARCHAR.
Declare input indicator variables (:TestDateInd and :LabTimeInd).
END DECLARE SECTION
.
.
.
INSERT
INTO MANUFDB.TESTDATA
(BatchStamp,
TestDate,
TestStart,
TestEnd,
LabTime,
PassQty,
TestQty)
VALUES (TO_DATETIME (:BatchStamp, :BatchStamp-Format),
TO_DATE (:TestDate :TestDateInd, :TestDate-Format),
TO_TIME (:TestStart :TestStartInd),
:TestEnd :TestEndInd,
TO_INTERVAL (:LabTime :LabTimeInd, :LabTime-Format),
:PassQty :PassQtyInd,
:TestQty :TestQtyInd)
Note that the user requested time data format is the default format.
Using the two time data columns in the TestData table (TestStart and
TestEnd), the above example illustrates two ways of specifying a default
format. Specify a date/time function without a format, or simply do not
use a date/time function.
Example Using the UPDATE Command.
These users want the capability of updating data based on the BatchStamp
column.
BEGIN DECLARE SECTION
Declare input host variables (:TestDate, :TestDate-Format, :BatchStamp,
and :BatchStamp-Format) to be compatible with data type CHAR or VARCHAR.
Declare input indicator variable (:TestDateInd).
END DECLARE SECTION
.
.
.
UPDATE MANUFDB.TESTDATA
SET TESTDATE = TO_DATE
(:TestDate :TestDateInd, :TestDate-Format),
TestStart = :TestStart :TestStartInd,,
TestEnd = :TestEnd :TestEndInd,,
LabTime = :LabTime :LabTimeInd,
PassQty = :PassQty :PassQtyInd,
TestQty = :TestQty :TestQtyInd
WHERE BatchStamp = TO_DATETIME
(:BatchStamp, :BatchStamp-Format)
Example Using the SELECT Command.
The users are planning to select data from the TestData table based on
the lab time interval between the start and end of a given set of tests.
BEGIN DECLARE SECTION
Declare input host variables (:BatchStamp, :BatchStamp-Format,
LabTime, and LabTime-Format) to be compatible with data type
CHAR or VARCHAR.
END DECLARE SECTION
.
.
.
SELECT BatchStamp
Testdate
TestStart,
TestEnd,
LabTime
PassQty,
TestQty
INTO :BatchStamp,
:TestDate :TestDateInd,
:TestStart :TestStartInd,
:TestEnd :TestEndInd,
:LabTime :LabTimeInd,
:PassQty : PassQtyInd,
:TestQty TestQtyInd
FROM MANUFDB.TESTDATA
WHERE LabTime > TO_INTERVAL (:LabTime, :LabTime-Format)
AND TO_DATETIME (:BatchStamp, :BatchStamp-Format),
BETWEEN :StampOne AND :StampTwo
Example Using the DELETE Command.
The users want to delete data from the TestData table by entering a value
for the BatchStamp column.
BEGIN DECLARE SECTION
Declare input host variables (:BatchStamp and :BatchStamp-Format)
to be compatible with data type CHAR or VARCHAR.
END DECLARE SECTION
.
.
.
DELETE FROM MANUFDB.TESTDATA
WHERE BatchStamp = TO_DATETIME (:BatchStamp, :BatchStamp-Format)