How to add / subtract / compare date with other date with example

Db2 gives very flexible support for the date time operations like Date addition (+) , Date Subtration (-) and Date Comparisons (<, ><=, >=,=) .In db2 , You can compare Date, Time and Timestamp with another value of the same type in chronological (in order from earliest to latest 1991, 1992, 1993)

Db2 supports string representation of date, time & timestamp where date or time can use ISO , USA , EUR or JIS format and timestamp uses ISO format. Example formats are given below.

ISO format Date : yyyy-mm-dd eg. 2011-09-09 / 2011-9-09 , Time : hh:mm:ss eg. 16:25:06

USA Format Date : mm/dd/yyyy eg. 9/09/2011 , Time : hh:mm AM / PM eg. 7.50 AM

EUR Format Date : dd.mm.yyyy eg. 09.09.2011 , Time : hh.mm.ss eg. 14.50.00 14.50

JIS Format Similar to ISO format.

Now let as see some examples using the above formats in queries for Date time arithmetic comparisons

Date Comparisons:

select * from employee where dob='1990-01-02'; where dob is a date field.

suppose you want to mention timestamp , the query may be represented as follows for the timestamp January 1st , 1995 9:50 AM

select * from employee where dob1='1995-01-01-09.50.00'; OR select * from employee where dob1='1995-01-01-09.50.00.0000';

In DB2 , decimal durations can be used in expressions (addition , subtraction) and comparisons. Arithmatic operations like + , - with date yields decimal durations. Decimal duration for Date will be having 8 digits , Time will be having 6 digits and TIMESTAMP will be having 20 digits. 8 digits of Date is in the format YYYYMMDD . For example 225 (MDD) represents 2 months and 25 days , 9990605 (YYYMMDD) is 999 years 6 months and 5 days. 6 digit of Time is in the format HHMMSS . For Example 102930 (HHMMSS) represents 10 Hrs , 29 min , 30 seconds. In general decimal(8,0) number represents the difference between two dates in the format YYYYMMDD. Decimal(6,0) number represents the difference between two time values as HHMMSS. A timestamp duration is a decimal(20,6) number representts the differences between two timestamp values as YYYYMMDDHHMMSS.ZZZZZZ (where ZZZZZZ is microseconds which will be in the right of the decimal point ) .

Examples for the concept decimal durations for date & time :

select DATE('2011-09-09') - '2011-07-25' from emp; which results 115 which means 1 month and 15 days

select DATE('2011-09-09') - '2011-07-09' from emp; results 200 which means 2 months and 0 days

Suppose if you want to calculate your age , just run the query select current date - 'dob' from anytable; . For Example, select current date - '1976-05-31' from emp; which results 350309 which means 35 years 3 months and 09 days

select DATE('09/09/2011') - '07/25/2011' + 10 from emp; results 125 which means 1 month and 25 days

select DATE('2011-09-09') + DECIMAL(315,8,0) from emp;

The above query adds 3 months and 15 days to the date 2011-09-09 which results the date 2011-12-24

select TIME('07:02:27') - '00:30:33' from emp; which results 63154 which is 6 hours, 31 minutes and 54 seconds.

select TIME('11:32:56') + DECIMAL(053030,6,0) from emp; outputs 17:03:26 is equal to 05.03.26 PM

Do you want to calculate , how many years your service is left ? If your retiredment age is 60 means , just run the query select DATE('Date_of_birth') + DECIMAL(600000,8,0) - current date from anytable;   .For example , select DATE('1976-05-31') + DECIMAL(600000,8,0) - current date from emp; whic results 240822 which means you have to serve 24 years 8 months and 22 days more.