Intelligent Enterprises of the 21st Century

DB2 provides many built-in supports for data manipulation. Because of the complexity involved with manipulating date, time, and string data, it is particularly important to understand how to use system-provided features on these data types.

Working with Dates and Times

Dates and times are the data types that differ the most among Database Management Systems (DBMSs). This section shows you examples of some of the basic date and time manipulations.

You can get the current date, time, and timestamp by using the appropriate DB2 special registers:

SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1; SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1; SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;

CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP are three DB2 special registers. Another useful DB2 special register for date and time operation is CURRENT TIMEZONE. You can use it to get the CURRENT TIME or CURRENT TIMESTAMP adjusted to GMT/CUT. All you need to do is to subtract the CURRENT TIMEZONE register from the CURRENT TIME or CURRENT TIMESTAMP:

SELECT CURRENT TIME - CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY1; SELECT CURRENT TIMESTAMP - CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY1;

Given a date, time, or timestamp, you can extract (where applicable) the year, month, day, hour, minutes, seconds, and microseconds portions independently using the appropriate function:

SELECT YEAR (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT MONTH (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;

You can also extract the date and time independently from a timestamp:

SELECT DATE (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT TIME (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

The date and time calculations are very straightforward:

SELECT CURRENT DATE + 1 YEAR FROM SYSIBM.SYSDUMMY1; SELECT CURRENT DATE + 3 YEARS + 2 MONTHS + 15 DAYS FROM SYSIBM.SYSDUMMY1; SELECT CURRENT TIME + 5 HOURS - 3 MINUTES + 10 SECONDS FROM SYSIBM.SYSDUMMY1;

DB2 also provides many date and time functions for easy date and time data manipulation. For a complete list, refer to the SQL Reference corresponding to your platform.

A few date and time functions are used here as examples to show you how you can work with date and time data in DB2. To calculate how many days there are between two dates, you can subtract dates as in the following:

SELECT DAYS (CURRENT DATE) - DAYS (DATE('2000-01-01')) FROM SYSIBM.SYSDUMMY1;

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 CHAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT CHAR(CURRENT TIME) FROM SYSIBM.SYSDUMMY1; SELECT CHAR(CURRENT TIME + 12 HOURS) FROM SYSIBM.SYSDUMMY1;

To convert a character string to a date or time value, you can use:

SELECT TIMESTAMP ('2002-10-20-12.00.00.000000') FROM SYSIBM.SYSDUMMY1; SELECT TIMESTAMP ('2002-10-20 12:00:00') FROM SYSIBM.SYSDUMMY1; -- For LUW, zSeries --SELECT TIMESTAMP '2002-10-20 12:00:00' FROM SYSIBM.SYSDUMMY1; -- For iSeries SELECT DATE ('2002-10-20') FROM SYSIBM.SYSDUMMY1; SELECT DATE ('10/20/2002') FROM SYSIBM.SYSDUMMY1; SELECT TIME ('12:00:00') FROM SYSIBM.SYSDUMMY1; SELECT TIME ('12.00.00') FROM SYSIBM.SYSDUMMY1;

Working with Strings

String manipulation is relatively easy compared with date and timestamps. Again, DB2 built-in functions are heavily used. A few of them are used in this section to show you how DB2 string operations work. For a complete list, refer to the SQL Reference corresponding to your platform.

You can use either the CONCAT function or the || operator for string concatenation. The following two statements are exactly the same:

SELECT CONCAT('ABC', 'DEF') FROM SYSIBM.SYSDUMMY1; SELECT 'ABC' || 'DEF' FROM SYSIBM.SYSDUMMY1;

However, when you have more than two strings to concatenate, the || operator is much easier to use.

You might have to use UPPER or LOWER function in string comparisons if you want the comparison to be case-insensitive. DB2 string comparison is case-sensitive.

COALESCE is another frequently used string function. It returns the first argument that is not null. In your application, if you have the following query SELECT coalesce(c1, c2, 'ABC') FROM t1;

assuming the c1 and c2 columns of table T1 are both nullable character strings, you will receive the value of c1 if it is not null. If c1 is null, you will receive the value of c2 if it is not null. If both c1 and c2 contain null values, you will receive the string ''ABC'' instead.