You can define columns in DB2 database tables in either "date," "time" or "timestamp" formats to store time-relevant information. Timestamp values include a date followed by the time, defined to six-position microsecond precision. A timestamp is useful when it is critical to know exactly when an event occurs or to sort data in entry order. Many applications use timestamps to record entry and update times for audit purposes.
- Skill level:
Other People Are Reading
Define a field that only contains 10 bytes. This example uses COBOL syntax in the working storage area. The "01" is the level indicator. "WS-DATE" is the field name. "PIC" indicates the picture clause with the "X(10)" indicating that the field is a 10 byte string.
01 WS-DATE PIC X(10).
Select a timestamp column from a DB2 table using a structured query language (SQL) statement. Type the following SQL statement into the program:
SELECT TIMESTAMP_VALUE FROM TEST_TABLE
Move the "timestamp_value" to the shorter working storage field to truncate all information after the date.
MOVE TIMESTAMP-VALUE TO WS-DATE.
If the timestamp contains "2010-01-01-10.30.02.123456," WS-DATE equals "2010-01-01." Use WS-DATE in "insert" or "update" statements, as well as in "where" clauses.
Convert the timestamp format to the "date" format using the "date" function in the "select" statement.
SELECT DATE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1
This statement returns the date portion of the current system timestamp. The sysdummy1 table is a special in-memory table provided by IBM for system functions that don't require a user-defined table. Substitute a user-defined table and column name to retrieve stored data.
If the current system timestamp is "2010-01-01-10.30.02.123456," the result of the SQL statement is "2010-01-01,"
Use the "cast" SQL function to convert a timestamp to a date in the "select" SQL statement. The "cast" statement reformats a value based on parameters within the parenthesis. In this example, the value is "current timestamp" and it is reformatted using the "date" format.
SELECT CAST(CURRENT TIMESTAMP AS DATE) FROM SYSIBM.SYSDUMMY1
If the current system timestamp is "2010-01-01-10.30.02.123456," the result of the SQL statement is "2010-01-01."
Verify that the "cast" and "date" functions return the same results by using them both within one SQL command.
SELECT DATE(CURRENT TIMESTAMP),
CAST(CURRENT TIMESTAMP AS DATE)
Tips and warnings
- DB2 will return an error if the SQL statement attempts to convert a value in an invalid format to a "date," "time" or "timestamp" format.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for