TechTip: SQL Date and Time Arithmetic PDF Print E-mail
Tips & Techniques - SQL
Written by Jim Cooper   
Friday, 20 February 2015 00:00

Support MC Press - Visit Our Sponsors

POPULAR FORUMS

Forums

Search Sponsor

POPULAR SEARCHES

Search

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.

 

Format name

Abbreviation

Format

Example

International Standards Organization (*ISO)

ISO

yyyy-mm-dd

'2011-04-07'

IBM USA Standard (*USA)

USA

mm/dd/yyyy

'04/07/2011'

IBM European Standard (*EUR)

EUR

dd.mm.yyyy

'07.04.2011'

Japanese Industrial Standard Christian era (*JIS)

JIS

yyyy-mm-dd

'2011-04-07'

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

 

order_date

ship_date

ship_date – order_date

2011-05-01

2011-05-15

00000014

2010-05-01

2011-05-15

00010014 (1 year, 0 months, 14 days)

 

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

 


Jim Cooper
About the Author:

Jim Cooper has spent his entire career on IBM systems, including the System/34, System/36, AS/400, and now System i. He worked as a software developer for many years before moving to the education sector. Jim has been Coordinator and Professor in the Computer Studies department at Lambton College in Sarnia, Ontario, Canada for more than 25 years. He has taught a variety of subjects, with his primary focus on IBM i application development skills.

From 1995 through 2001, Jim served as Director of the IBM Roundtable College Conference, an IBM international conference held annually for colleges teaching IBM i technologies. Jim has also authored two textbooks on RPG and COBOL programming with John Wiley & Sons.

Read More >>
Last Updated on Thursday, 19 February 2015 11:25
 
User Rating: / 1
PoorBest 
   MC-STORE.COM