Datacom

Tech Tip: How to insert values into SQL Timestamp columns

  • 1.  Tech Tip: How to insert values into SQL Timestamp columns

    Broadcom Employee
    Posted 02-02-2016 01:05 PM

    Tech Tip for Tuesday, 2 February 2016, taken from TEC1518921:

    Introduction:

    Working with SQL Timestamp columns requires that values be referenced in a specific format. As a result, if using INSERT statements, you will need to provide the timestamp value in a particular edited format, and this value is constant regardless of whether you are using ISO, USA, EUR, or JIS string format for other columns.

    Instructions:

    To use an INSERT statement for a timestamp value, please follow the format as noted in the CA Datacom/DB SQL User Guide. This value contains the year, month, day, hour, minute, second, and microseconds. For example, to insert a row with the timestamp of 29 January 2016, at 1:34:26.184232 pm, you would use:

    INSERT INTO tblname
        VALUES (…,'2016-01-29-13.34.26.184232', …);

    Likewise, when retrieving a timestamp value from a table, even though the column is stored in CA Datacom/DB as a 10-byte Binary field, it is returned in the same character form as above.

    Additional Information:

    For more information about the use of the Timestamp data type, please refer to the following Guides:

    CA Datacom/DB version 14.02 SQL User’s Guide

    CA Datacom/DB version 15.0 SQL User’s Guide

    As always, please contact CA Technologies support for CA Datacom if you have further questions.