subtract of dates

[Lesio]

What is the most efficient way of subtracting two dates in order that the result was in number of months ?

Example:
Date1 = 2002-01-14
Date2 = 2003-01-17

Date2 - Date1 = 13

 

[swampBoogie]

(date2 - date1) month(2)

 

[tdatgod]

month(2)

limits the answer to a maximum of 2 digits up to 99 months or 8.3 years. If your answer is more than 2 digits ( let us say 10 years )

Date1 = 1993-01-14
Date2 = 2003-01-17

Date2 - Date1 = 121

you will get a numeric overflow error.


You might want to consider

(date2 - date1) month(4)

which allows for a maximum of 4 digit answers up to 9999 or 833 years.

4 is the largest value allowed for Months.

---

 

[Lesio]

Month(2) looks to work great ! Thanks !

Last two question:

1. Is there a more performance efficient way to convert a date (YYYY-MM-DD) to the first day of the month (YYYY-MM-01) than expression for date1 in the SQL statement below ?

Example: Convert(2003-01-20) = 2003-01-01

2. Is there a more performance efficient way to convert a date in integer type (YYYYMM) to the same result date format (YYYY-MM-01) than expression for date2 in the SQL statement below ?

Example: Convert(200301) = 2003-01-01


select distinct (date1/100 || '01') (char(15)) (integer) (date) date1
,((date2_yyyymm -190000) || '01') (char(15)) (integer) (date) date2
,(date1 - date2 ) month(2) as "difference"
from table_name

 

[dnoeth]

1.
select current_date - (extract(day from current_date) + 1);

2.
select (200301*100) - 18999999 (date)

Dieter