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 “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
^[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)

Using Date/Time Input Functions

Technical documentation

» Table of Contents

» Index

Examples of TO_DATETIME, TO_DATE, TO_TIME, and TO_INTERVAL Functions

Example Using the INSERT Command

Example Using the UPDATE Command

Example Using the SELECT Command

Example Using the DELETE Command