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.