ALLBASE/SQL Reference Manual (36216-90216)

250 Chapter8
Expressions
Date/Time Functions
DATE or DATETIME column. Refer to the Add Months Function for further
information.
Examples
1. Date format
In the example below, the format MM/DD/YY is used to enter a date instead of using the
default format, which is YYYY-MM-DD:
INSERT INTO ManufDB.TestData(batchstamp, testdate)
VALUES (TO_DATETIME ('07/02/89 03:20.000', 'MM/DD/YY HH12:MI.FFF'),
TO_DATE('10/02/84','MM/DD/YY'))
To return the date entered in the above example, in a format other than the default
format, the desired format is specified in the second parameter of the TO_CHAR
conversion function:
SELECT TO_CHAR(testdate, 'Dayofweek, Month DD')
FROM ManufDB.TestData
WHERE labtime < '0 05:00:00.000'
The value "Friday, July 13" is selected from TestData.
The following statement inserts different date values depending on the value of the
environment variable HPSQLsplitcentury, if it is set.
INSERT INTO ManufDB.TestData(testdata)
VALUES (TO_DATE ('30/10','YY/MM'))
Case 1: HPSQLsplitcentury is not set; inserts 2030-10-01
Case 2: HPSQLsplitcentury is set to 0; inserts 1930-10-01
Case 3: HPSQLsplitcentury is set to 70; inserts 2030-10-01
2. Time format
INSERT INTO ManufDB.TestData(teststart, batchstamp)
VALUES (TO_TIME('01:53 a.m.','HH12:MI a.m.'),
TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM'))
3. Datetime format
UPDATE ManufDB.TestData
SET batchstamp = TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM')
WHERE batchstamp = TO_DATETIME('11.01.84 1.11 PM', 'DD.MM.YY HH12.MI PM')
4. Interval format
UPDATE ManufDB.TestData
SET labtime = TO_INTERVAL('06 10:12:11.111', 'DAYS HH:MI:SS.FFF')
WHERE testdate = TO_DATE('10.02.84','MM.DD.YY')