Date-Time-Timestamp use in SQL Queries

The sysibm.sysdummy1 table is a special in-memory table that can be used to fetch the value of DB2 registers Date-Time-Timestamp. To extract a certain portion of data from the current timestamp you can use the below-mentioned functions. Specifying CURRENT_TIMESTAMP is equivalent to specifying CURRENT TIMESTAMP.

Date-Time-Timestamp

SELECT current date FROM sysibm.sysdummy1 
SELECT current time FROM sysibm.sysdummy1  
SELECT current timestamp FROM sysibm.sysdummy1 

You can extract certain parts of the timestamp by using the below-mentioned functions.

DATE (current timestamp) 
TIME (current timestamp)

• YEAR (current timestamp)
• MONTH (current timestamp)
• DAY (current timestamp)
• HOUR (current timestamp)
• MINUTE (current timestamp)
• SECOND (current timestamp)
• MICROSECOND (current timestamp)

You can use calculation with DB2 date-time functions.

CURRENT_DATE = current date + 3 YEARS + 2 MONTHS + 15 DAYS

Calculate how many days there are between two dates

days (current date) ‑ days (date(‘1999‑10‑22’))

Example 1: Display information about all rows that were inserted or updated in the last week.

SELECT * FROM EMP WHERE TIMESTAMP > CURRENT TIMESTAMP - 7
DAYS;

To convert a character string to a date or time value, you can use the TIMESTAMP(), DATE(), and TIME() functions used to convert values into timestamp, date, and time formats.

SQL Date and Time functions:

DAYNAME: Returns a mixed case character string containing the name of the day (e.g., Friday) for the day portion of the argument.

DAYOFWEEK: Returns the day of the week in the argument as an integer value in the range 1-7, where 1 represents Sunday.

DAYOFYEAR: Returns the day of the year in the argument as an integer value in the range 1-366.

DAYS: Returns an integer representation of a date.

JULIAN_DAY: Returns an integer value representing the number of days from January 1, 4712 B.C. (the start of Julian date calendar) to the date value specified in the argument.

MONTHNAME: Returns a mixed case character string containing the name of month (e.g., January) for the month portion of the argument.

WEEK: Returns the week of the year of the argument as an integer value in range 1-54. The week starts with Sunday.

Common SQL Error code related to Date-Time-Timestamp:

-180   THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID

Explanation: The length or a string representation of a DATE, TIME, or TIMESTAMP value does not conform to any valid format. The value can contain one of the following:

• For a host variable, the position number of the input host variable. If the position number cannot be determined, a blank is displayed.
• For a character string constant, the character string constant. The maximum length that is displayed is the length of SQLERRM.
• For a character column, the column name. If the column is a VIEW column and it has a corresponding base column, the VIEW column name is displayed. If the column is a VIEW column but it does not have a corresponding base column, a string of ‘*N’ is displayed.
• Otherwise, value is a string of ‘*N’.

-181   THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE (MM>12, DD>31, 30, 29, 28 depending on the month)

Explanation: The string representation of a date-time is not in the acceptable range or is not in the correct format. e.g. Months can be between 01-12, days can be as per the month.

-182   AN ARITHMETIC EXPRESSION WITH A DATETIME VALUE IS INVALID

Explanation: The specified arithmetic expression contains an improperly used date-time value or labeled duration.

-183   AN ARITHMETIC OPERATION ON A DATE OR TIMESTAMP HAS A RESULT THAT IS NOT WITHIN THE VALID RANGE OF DATES

Explanation: The result of an arithmetic operation is a date or timestamp that is not within the valid range of dates which are between 0001-01-01 and 9999-12-31.

Read DB2 blogs : Click Here IBM DB2 Manual :Click Here