HP 3000 Manuals

ALLBASE/SQL C Application Programming Guide

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  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

MPE/iX 5.0 Documentation