DB2 - SQL Date, Time Functions

Following is the list of commonly used Date and Time Manipulation Functions:

Let use see examples below,

Example 1:

How to get current date, time and timestamp ?

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

Example 2:

How to get year, month, day, hour, minutes, seconds, and microseconds from current timestamp ?

SELECT YEAR (current timestamp) FROM sysibm.sysdummy1;
SELECT MONTH (current timestamp) FROM sysibm.sysdummy1;
SELECT DATE (current timestamp) FROM sysibm.sysdummy1;
SELECT TIME (current timestamp) FROM sysibm.sysdummy1;
SELECT DAY (current timestamp) FROM sysibm.sysdummy1;
SELECT HOUR (current timestamp) FROM sysibm.sysdummy1;
SELECT MINUTE (current timestamp) FROM sysibm.sysdummy1;
SELECT SECOND (current timestamp) FROM sysibm.sysdummy1;
SELECT MICROSECOND (current timestamp) FROM sysibm.sysdummy1;

Example 3:

How to perform date and time calculations ?

SELECT current date + 2 YEAR FROM sysibm.sysdummy1;
SELECT current date + 1 YEARS + 10 MONTHS FROM sysibm.sysdummy1;
SELECT current date + 1 YEARS + 5 MONTHS + 10 DAYS FROM sysibm.sysdummy1;
SELECT current time + 5 HOURS ‑ 3 MINUTES + 10 SECONDS FROM sysibm.sysdummy1;

Example 4:

How to find number of days between two dates, you can subtract dates as below:

SELECT days (current date) ‑ days (date('1990‑09-02')) FROM sysibm.sysdummy1;

Example 5:

If you want to concatenate date or time values with other text, you need to convert the value into a character string first. To do this, you can simply use the CHAR() function:

SELECT "Current Date -" || char(current date) FROM sysibm.sysdummy1;
SELECT "Current Time -" || char(current time) FROM sysibm.sysdummy1;
SELECT "Current Date with 12 hours -" char(current date + 10 hours) FROM sysibm.sysdummy1;

Example 6:

If you want to convert a character string to a date or time value, you can use as below:

SELECT TIMESTAMP ('1990‑09‑02‑12.00.00.000000') FROM sysibm.sysdummy1;
SELECT TIMESTAMP ('1990‑09‑02 12:00:00') FROM sysibm.sysdummy1;
SELECT DATE ('1990‑09‑02') FROM sysibm.sysdummy1;
SELECT DATE ('02/09/1990') FROM sysibm.sysdummy1;
SELECT TIME ('10:00:00') FROM sysibm.sysdummy1;
SELECT TIME ('10.00.00') FROM sysibm.sysdummy1;

Example 7:

How to change Date/Time Format ? Let us see formatting date in various formats.

SELECT
    CHAR(DATE (CURRENT TIMESTAMP),ISO) AS date_in_iso,
    CHAR(DATE (CURRENT TIMESTAMP),USA) AS date_in_usa,
    CHAR(DATE (CURRENT TIMESTAMP),EUR) AS date_in_eur,
    CHAR(DATE (CURRENT TIMESTAMP),JIS) AS date_in_jis
FROM sysibm.sysdummy1;

Result:

Example 8:

Let us see how to convert date format using VARCHAR_FORMAT function.

SELECT
    VARCHAR_FORMAT(CURRENT TIMESTAMP,'YYYY-MM-DD') AS VCHAR_FORMATED,
    DATE(CURRENT TIMESTAMP) AS DATE_VALUE
FROM sysibm.sysdummy1;

Example 9:

The following SQL statement shows how to use VARCHAR_FORMAT function in where clause.

SELECT * FROM Employee_table
    WHERE VARCHAR_FORMAT (date_col,'YYYY-MM-DD') = '1990-09-02'

Other important Date and Time functions are as follows:

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

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

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

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

• MIDNIGHT_SECONDS: Returns an integer value in the range 0 to 86 400 representing the number of seconds between midnight and the time 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.

• TIMESTAMP_ISO: eturns a timestamp value based on date, time or timestamp argument.

• TIMESTAMP_FORMAT: Returns a timestamp from a character string that has been interpreted using a character template.

• TIMESTAMPDIFF: Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.

• TO_CHAR: Returns a character representation of a timestamp that has been formatted using a character template. TO_CHAR is a synonym for VARCHAR_FORMAT.

• TO_DATE: Returns a timestamp from a character string that has been inter-preted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT.

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

• WEEK_ISO: Returns the week of the year of the argument as an integer value in the range 1-53.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!