Date Arithmetic

By: Borland Staff

Abstract: Date arithmetic examples for InterBase 4.x and 5.x

Problem:
I need examples of date arithmetic.

Solution:
IB V4.x
IB V5.x


A simple arithmetic select statement between two date fields return
a value that is a fraction of a day:

select (table2.date_fld-table1.date_fld) as Simple_Select from table1,table2
where table1.date_fld=table2.date_fld;

         SIMPLE_SELECT 
====================== 

		 30 
     58.04305555555766 
		 30
		 29

To perform a date calculation that ignores the hours and minutes
portion (remainder):

select cast  ((table2.date_fld-table1.date_fld) as integer) as No_HRS_MINS
from table1,table2 where table1.date_fld=table2.date_fld;

NO_HRS_MINS 
=========== 

         30 
         58 
         30 
         29 

To perform a date calculation that returns hours and minutes:select cast  
(24*(table2.date_fld-table1.date_fld) as integer) as hours,
cast((((table2.date_fld-table1.date_fld)*24)-
cast((table2.date_fld-table1.date_fld)*24 as integer)) *60 as integer)
as minutes from table1,table2 where table1.date_fld=table2.date_fld;

      HOURS	 MINUTES 
===========      =========== 

	720		0 
                 1393		2 
	720		0 
	696		0 


In the last query, the following intermediate result is calculated:

Line 1: Total number of hours
Line 2: Total number of hours with minutes (remainder).
Line 3: The inner set of parenthesis calculates the total number of
hours without minutes (thanks to the cast operator).  Then this
value is subtracted from Line 2's and multiplied by 60 to get the
total number of minutes (the cast operator is used to ignore seconds).

Server Response from: ETNASC03