 |
» |
|
|
|
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 “Where to Use Date/Time Functions”.
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 “Sample of User Requested Formats for Date/Time Data”. 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
|
You might use the following generic code examples to meet their needs. Example Using the INSERT CommandYour 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 CommandThese 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 CommandThe 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 CommandThe 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)
|
|
