Using Date/Time Output Functions

Specify the output format of any type of date/time column by using a date/time output function. Use an output function with any DML operation listed in Table 11-2 “Host Variable Data Type Compatibility for Date/Time Functions” with one exception. In the case of a [BULK] INSERT command, output functions are limited to use in the select list and the WHERE clause of a Type 2 INSERT command.

As with date/time input functions, use a host variable or a literal string to indicate a format specification. See the ALLBASE/SQL Reference Manual for detailed syntax.

Following is the general syntax for date/time output functions:

{TO_CHAR (ColumnName [,FormatSpecification])} { TO_INTEGER (ColumnName, FormatSpecification)}

Example TO_CHAR Function

The default format for the DATETIME data type specifies the year followed by the month followed by the day. The default format for the TIME data type specifies a 24-hour clock. (Refer to the ALLBASE/SQL Reference Manual .)

Suppose users located in Italy want to input a specified batch stamp to obtain the start and end times of the related test in 12-hour format. They will key the batch stamp in this format, "DD-MM-YYYY HH12:MM:SS:FFF AM or PM." The times returned will be in this format, "HH12:MM:SS.FFF AM or PM."

Data is located in the TestData table in the manufacturing database. (Refer to appendix C in the ALLBASE/SQL Reference Manual .) The following code could be used:

   BEGIN DECLARE SECTION



   Declare input host variables (:TwelveHourClockFormat, :BatchStamp,

   :ItalianFormat, and :SpecifiedInput) to be compatible with data type 

   CHAR or VARCHAR.



   Declare output host variables (:TestStart and :TestEnd) to be compatible 

   with data type CHAR or VARCHAR . 



   Declare output indicator variables (:TestStartInd and :TestEndInd).



   END DECLARE SECTION

   .

   .

   .

   SELECT TO_CHAR(TestStart, :TwelveHourClock),

          TO_CHAR(TestEnd, :TwelveHourClock) 

     INTO :TestStart :TestStartInd,

          :TestEnd :TestEndInd,

     FROM ManufDB.TestData

     WHERE TO_DATETIME(:BatchStamp, :ItalianFormat) = :SpecifiedInput

Note the use of indicator variables in the above example. Because the TO_CHAR function is used in the select list, no need exists to specify an indicator variable as part of the function.

Example TO_INTEGER Function

The TO_INTEGER format specification is mandatory and differs from that of other date/time functions in that it must consist of a single element only. See the ALLBASE/SQL Reference Manual for detailed format specifications.

Perhaps you are writing a management report that indicates the quarter of the year in which tests were performed. (As in the previous example, data is located in the TestData table in the manufacturing database.) You could use the following code:

   BEGIN DECLARE SECTION



   Use the ALLBASE/SQL Reference Manual  to determine your desired format 

   specification.  (In this case it is Q.) 



   Declare the input host variable, :QuarterlyFormat, to be compatible with data 

   types CHAR or VARCHAR.



   In the ReportBuffer array, declare an output host variable (:TestDateQuarter)

   to be compatible with data type INTEGER.  Declare other output host 

   variables (:BatchStamp, :LabTime, :PassQty, and :TestQty) to be 

   compatible with data type CHAR or VARCHAR.



   Remember to declare output indicator variables (:TestDateQuarterInd, 

   LabTimeInd, PassQtyInd, and :TestQtyInd) in the ReportBuffer array.



   END DECLARE SECTION

   .

   .

   .

   DECLARE ReportInfo CURSOR FOR

                          SELECT BatchStamp, 

                                 TO_INTEGER(TestDate, :QuarterlyFormat),

                                 LabTime,

                                 PassQty, 

                                 TestQty

                            FROM ManufDB.TestData

   .

   .

   .

   BULK FETCH ReportInfo

         INTO ReportBuffer

Using Date/Time Output Functions

Technical documentation

» Table of Contents

» Index

Example TO_CHAR Function

Example TO_INTEGER Function