Programming with Date/Time Functions

Seven functions can be used with date/time data types. These functions provide flexibility for inputting and retrieving date/time data from the database.

These functions can be used with a preprocessed application or with ISQL. This chapter outlines basic principles for using date/time functions in an application program. The following sections are included:

Where Date/Time Functions Can Be Used.
Defining and Using Host Variables with Date/Time Functions.
Using Date/Time Input Functions.
Using Date/Time Output Functions.
Using the Date/Time ADD_MONTHS Function.
Coding Considerations.
Program Examples for Date/Time Data.

Date/time functions are used as you would use an expression. And when used in a select list, all date/time functions produce data output. Refer to the section in this chapter, "Defining and Using Host Variables with Date/Time Functions."

Suppose for example that you are programming for an international corporation. Your database tables contain various date/time columns and the data is used by employees in several countries. You write a generic program on which you base a set of customized programs, one for each geographical location. Each customized program allows the employees at a given location to input and retrieve date/time information in the formats with which they are most comfortable.

Refer to the "Host Variables" chapter for more information on date/time data types. Complete syntax and format specifications for date/time functions are found in the ALLBASE/SQL Reference Manual in the "Expressions" and "Data Types" chapters.




	NOTE: For all date/time functions, character input and output values are in Native-3000 format.

Where Date/Time Functions Can Be Used

Use date/time functions, as you would an expression, in the DML operations listed below:

Table 12-1 Where to Use Date/Time Functions

DML Operation	Clause
INSERT ^[1]	VALUES
	WHERE
UPDATE or	SET
UPDATE WHERE CURRENT	WHERE
DELETE or	WHERE
DELETE WHERE CURRENT
SELECT	Select list ^[2]
	WHERE
DECLARE	Select list ^[2]
	WHERE
^[1]In the case of a INSERT, output functions, TO_CHAR and TO_INTEGER, and the ADD_MONTHS function, are limited to use in the select list and the WHERE clause of a Type 2 INSERT. ^[2]Input functions, TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL, are generally not appropriate in a select list.

Defining and Using Host Variables with Date/Time Functions

Date/time functions can be used in the way an expression is used; that is, in a select list to indicate the columns you want in the query result, in a search condition to define the set of rows to be operated on, and to define the value of a column when using the UPDATE command. (See the ALLBASE/SQL Reference Manual for in-depth information regarding expressions.)

Whether you use host variables or literal strings to specify the parameters of the date/time functions depends on the elements of your application and on how you are using the functions. This section focuses on the use of host variables.

You can use host variables to specify input or output format specifications. Use them as well to hold data input to and any resulting data output from the date/time functions. (Host variables cannot be used to indicate column names.)

Host variables for format specifications must be defined in your application to be compatible with ALLBASE/SQL CHAR or VARCHAR data types. The exception is the ADD_MONTHS function which requires an INTEGER compatible host variable.

As for host variables containing input and output data, define them to be CHAR or VARCHAR compatible with one exception. The TO_INTEGER function requires an INTEGER compatible host variable for its output.

Reference the chapter on defining host variables for additional information about defining a host variable to be compatible with a specific ALLBASE/SQL data type. Note that the declarations relate to the default format specification for each date/time data type. Your declaration must reflect the length of the format you are using.

Table 12-2 “Host Variable Data Type Compatibility for Date/Time Functions” shows host variable data type compatibility for date/time functions.

Table 12-2 Host Variable Data Type Compatibility for Date/Time Functions

Date/Time Function	Input Format Specification	Output Format Specification	Input Data	Output Data
TO_DATE	(VAR)CHAR		(VAR)CHAR	(VAR)CHAR ^[1]
TO_TIME
TO_DATETIME
TO_INTERVAL
TO_CHAR		(VAR)CHAR		(VAR)CHAR
TO_INTEGER		(VAR)CHAR		INTEGER
ADD_MONTHS	INTEGER			(VAR)CHAR ^[1]
^[1]Applies only when used in a select list.

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 12-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 12-3 “Sample of User Requested Formats for Date/Time Data”.

Table 12-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 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 12-2 “Host Variable Data Type Compatibility for Date/Time Functions” with one exception. In the case of a 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.



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



   END DECLARE SECTION

   .

   .

   .

   DECLARE ReportInfo CURSOR FOR

                          SELECT BatchStamp, 

                                 {{TO_INTEGER(TestDate, :QuarterlyFormat),}}

                                 LabTime,

                                 PassQty, 

                                 TestQty

                            FROM ManufDB.TestData

   .

   .

   .

        FETCH ReportInfo

         INTO ReportBuffer :BatchStamp  

                           :TestDateQuarter  :TestDateQuarterInd

                           :LabTime          :LabTimeInd

                           :PassQty          :PassQtyInd

                           :TestQty          :TestQtyInd

Using the Date/Time ADD_MONTHS Function

This function allows you to add an integer number of months to a DATE or DATETIME column. Do so by indicating the number of months as a positive, negative, or unsigned integer value. (An unsigned value is assumed positive.) Also, you can specify the integer in a host variable of type INTEGER.

The ADD_MONTHS function can be used in both input and output operations as shown in Table 12-1 “Where to Use Date/Time Functions”.

Following is the general syntax for the ADD_MONTHS function:

{ADD_MONTHS (ColumnName, IntegerValue)}

As with date/time output functions, use the ADD_MONTHS function with any DML operation listed in Table 12-2 “Host Variable Data Type Compatibility for Date/Time Functions” with one exception. In the case of a INSERT command, the ADD_MONTHS function is limited to use in the select list and the WHERE clause of a Type 2 INSERT command.

Example ADD_MONTHS Function

Perhaps you want to increment each date in the TestDate column by one month in the ManufDB.TestData table of the manufacturing database. The following command could be used:

    

        UPDATE ManufDB.TestData

           SET TestDate = ADD_MONTHS (TestDate, 1);

Coding Considerations

The following list provides helpful reminders when you are using date/time functions:

Input functions require leading zeros to match the fixed format of an element. (Z is not supported.)
For all date/time functions, when you provide only some elements of the complete format in your format specification, any unspecified elements are filled with default values.
Arithmetic operations are possible with functions of type INTEGER.
The length of the data cannot exceed the length of the format specification for that data. The maximum size of a format specification is 72 bytes.
Because LIKE works only with CHAR and VARCHAR values, if you want to use LIKE with date/time data, you must first convert it to CHAR or VARCHAR. For this you can use the TO_CHAR conversion function.
MIN, MAX, COUNT can be used with any DATE/TIME column type. SUM, AVG can be used with INTERVAL data only.
Do not specify an indicator variable as a parameter of a date/time function used in the select list of a query.
When using the ADD_MONTHS function, if the addition of a number of months (positive or negative) would result in an invalid day, the day field is set to the last day of the month for the appropriate year, and a warning is generated indicating the adjustment.