calculate number of started months

Wim · #1 (**permalink**) 04-26-06, 17:07

I should calculate the number of months between two dates. If a month is started for 1 day or 20, it should be counted as one month.
eg:
start date 01 jan 2006

with end date 30 jan 2006 : 1 month
with end date 01 feb 2006 : 1 month
with end date 02 feb 2006 : 2 months
with end date 28 feb 2006 : 2 months
with end date 01 mar 2006 : 2 months
with end date 02 mar 2006 : 3 months

I've been thinking of using:
MONTH(DA_LAST) - MONTH(DA_FIRST) +
CASE WHEN DAY(DA_LAST) > DAY(DA_FIRST) THEN 1 ELSE 0 END

but it fails with eg
start date 01 dec 2005 and end date 30 jan 2006
start date 28 feb 2006 and end date 30 mar 2006

I will change job (datawarehouse and datamining) within a few days and I would like to finish my current project before leaving - I have great end users -. Can you help me ?

wrongsachin · #2 (**permalink**) 04-26-06, 18:02

declare @First datetime
declare @Last datetime
set @First = '2006-01-01'
set @Last = '2006-03-02'

select
case
when month(@last) = month(@first)
then
1
else
month(@last) - month(@first) +
(case when day(@last) = 1 then 0 else 1 end)
end

Wim · #3 (**permalink**) 04-26-06, 20:06

This suffers from the same problem as my first attempt, it doesn't handle
da_first = '2005-12-30'
da_last = '2006-01-02'

I came up with this

Code:

SELECT (
	(MONTH(da_end) - MONTH(da_start)) +
	(12 * (YEAR(da_end) - YEAR(da_start))) +
	(CASE WHEN DAY(da_end) > DAY(da_start) THEN 1 else 0 END)
	) as nbr_months
FROM (SELECT cast('2006-02-28' as date) as da_start,
		cast('2006-03-30' as date) as da_end
	FROM "SYSIBM"."SYSDUMMY1"
	) as T
;

it handles da_start = '2005-12-30' and da_end = '2006-01-02' correct
but it can't handle the current example: it should return 1, but it returns 2

n_i · #4 (**permalink**) 04-27-06, 16:56

I think this will work:

Code:

select (
   int((date('2006-03-02') - date('2006-01-01'))/100) +  
   sign(mod(int(date('2006-03-02') - date('2006-01-01')),100))
) from sysibm.sysdummy1

If your dates can span more than a year you'll need to account for that. Subtraction of two dates yields a decimal value with the following format: "yymmdd".

Internet Services

Web Development

Digital Marketing

Consumer Tech