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).
Last Modified: 23-SEP-00