April 24


How to initialize a timestamp variable using IBM i SQL

By NickLitten

April 24, 2024

AI Generated Answers

More SQL Basics from the AI Archives

How to initialize a timestamp variable using ibm i sql

To initialize a timestamp in IBM i SQL, you can use the CURRENT TIMESTAMP special register which specifies a timestamp based on the reading of the time-of-day clock when the SQL statement is executed.

If you need a timestamp with a specific precision, you can reference it as CURRENT TIMESTAMP(integer), where integer can range from 0 to 12, with the default precision being 6.

Here’s an example of how you might use it in an SQL statement:

INSERT INTO your_table_name
VALUES (CURRENT TIMESTAMP, :column2, :column3, ...)

This will insert a new row into your_table_name with the current timestamp and the values for the other columns provided by the host variables or literals [more]

Additionally, if you want to combine date and time to create a timestamp, you can use the %DATE and %TIME built-in functions like this:

timestamp = %date(numdate : *ymd) + %time(numtime : *iso);

This will create a timestamp by adding the date and time together [more].

Remember to replace numdate and numtime with your actual numeric date and time values.

  • Why would you concatenate date and time if you can just use the build in function %Timestamp ? (ie. timestamp = %Timestamp)

    Concatenating these can have a potential issue on end of day that would result in a date from yesterday with a time of today.

    • Assume he is showing an example of building the timestamp if you have two variables already initialized with values and you are not looking for the current timestamp.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips