How do I convert the datestamp date in db2?

Written by sara wayne
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How do I convert the datestamp date in db2?
Exact times are useful in DB2 applications. (time image by Deborah Durbin from Fotolia.com)

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

Other People Are Reading

Instructions

  1. 1

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

  2. 2

    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

  3. 3

    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.

  1. 1

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

  2. 2

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

  3. 3

    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)

    FROM SYSIBM.SYSDUMMY1

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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.