Micro Focus Enterprise Developer 3.0 for Eclipse > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > Functions Overview > Date/Time Arithmetic > Subtracting Times

Subtracting Times

Restriction: This topic applies to Windows environments only.

In DB2 mode, when one time is subtracted from another, the result is a time duration that indicates the number of hours, minutes and seconds between the two times. For example, the result of:

TIME('10:22:16') - '00:34:46'

is 94730, (duration of 9 hours, 47 minutes and 30 seconds). The data type of the result is DECIMAL(6,0).

The rules that apply to the time subtraction calculation are as follows:

If TIME1 is greater than or equal to TIME2, then TIME2 is subtracted from TIME1.
If TIME1 is less than TIME2, TIME1 is subtracted from TIME2 and the result is negative.

Using the operation RESULT = TIME1 - TIME2, these rules can be illustrated as follows:

If  SECOND(TIME2) <= SECOND(TIME1)
  Then  SECOND(RESULT) = 
    SECOND(TIME1) - SECOND(TIME2)
If SECOND(TIME2) > SECOND(TIME1)
  Then  SECOND(RESULT) = 
    60 + SECOND(TIME1) - SECOND(TIME2)
  And  MINUTE(TIME2) is then incremented by 1
If MINUTE(TIME2) <= MINUTE(TIME1)
  Then  MINUTE(RESULT) = 
    MINUTE(TIME1) - MINUTE(TIME2)
If MINUTE(TIME2) > MINUTE(TIME1)
  Then  MINUTE(RESULT) = 
    60 + MINUTE(TIME1) - MINUTE(TIME2)
  And  HOUR(TIME2) is incremented by 1
HOUR(RESULT) = HOUR(TIME1) - HOUR(TIME2)