Are you looking for a hands-on approach that provides start-to-finish coverage of database design and SQL?

Editor's note: This TechTip is an excerpt from Chapter 6, "Single-Table Queries," of Database Design and SQL for DB2 (MC Press, 2013).

SQL on IBM i includes expressions and functions for working with date and time values. Externally, date values are character strings containing numbers for the year, month, and day and (in some formats) date separators. When a literal date is entered, the value is coded as a string. The format of the string depends on the values specified for the DATFMT and DATSEP parameters. For example, if *ISO format is specified, the following string represents May 1, 2011:

'2011-05-01'

With *USA format, the proper representation is

'05/01/2011'

SQL offers some flexibility in the format of date literals, and it automatically recognizes a literal that is coded in one of the standard formats listed in Table 1. These formats can be used regardless of the value specified for the DATFMT parameter.

Table 1: Standard date literal formats

SQL also provides date addition and subtraction and date functions. In the example in Figure 1, the DAYS function is used to obtain the number of days since January 1, 0001. The difference between the DAYS value for ShipDate and the DAYS value for order_date is then used to determine how long after the sale the order was shipped.

Example:

Retrieve customer_id, order_id, order_date, ship_date, and Days To Ship (difference between ship_date and order_date in days) for all rows in the ORDERS table where ship_date is not null.

SQL:

SELECT customer_id,

       order_id,

          order_date,

          ship_date,

          DAYS( ship_date ) - DAYS( order_date )

         AS "Days To Ship"

FROM orders

WHERE ship_date IS NOT NULL;

Results:

CUSTOMER_ID ORDER_ID ORDER_DATE SHIP_DATE Days To Ship

----------- -------- ---------- --------- ------------

     499320   234112 05/01/11   05/15/11            14

     888402   234113 05/01/11   05/04/11             3

     890003   234115 05/04/11   05/10/11             6

     246900   234116 05/04/11   05/08/11             4

     133568   234117 05/05/11   05/08/11             3

     905011   234118 05/05/11   05/10/11             5

      246900   234120 05/06/11   05/08/11             2

Figure 1: Retrieval using dates and durations

Notice how for the first row, order_date is 05/01/11 and ship_date is 05/15/11, which results in a difference of 14 days between the DAYS function values for the two columns. If order_date were 05/01/10 (notice the year) and ship_date were 05/15/11, the difference between the DAYS value for the two columns would be 380 days (366 + 14).

Note also how the search condition in this example excludes rows with a NULL ship_date. Because the ORDERS table permits the ship_date column to be null, the query must consider the case in which a row has a null ship_date. For arithmetic expressions, if one of the operands is null, the result of the expression is null. This rule makes sense, as can be seen in this example, in which the difference between two dates is obviously unknown if one or both of the dates is unknown, or null. The IS NOT NULL search condition lets the query eliminate the cases where the ship_date is not known, as when an order has not yet been shipped.

As an alternative, the following expression could be used to calculate the difference between two dates as an SQL date duration value:

ship_date – order_date

The result of this expression for sample column values would be

SQL represents date duration as an 8-digit decimal number in the form yyyymmdd, not as a number of days; do not confuse date duration with days duration.

Duration can also be added to or subtracted from a date. When order_date is 2011-05-01, the result of the expression

order_date + 14 Days

is 2011-05-15. In this example, the term 14 Days is a labeled duration. The following keywords can be used to specify what a duration value represents: YEARS, MONTHS, DAYS, HOURS, MINUTES, SECONDS, or MICROSECONDS. The following expression adds one year, two months, and 14 days to a date:

order_date + 1 Year + 2 Months + 14 Days

SQL supports time and timestamp columns, literals, and arithmetic in a way similar to what has been covered for date arithmetic. To code a time value with SQL's ISO format, a string representation, such as '13.30.10' for 10 seconds after 1:30 p.m., is used. To use a colon (:) as the time separator (e.g., '13:30:10'), use the JIS format. Time duration is a 6-digit DECIMAL number with an hhmmss format. Labeled time durations can also be used, as in the following expression, which adds labeled time duration values to a time column:

WrkBgnTime + 1 Hour + 30 Minutes