# Thread: calculate number of started months

1. Registered User
Join Date
Nov 2004
Posts
1,428

## Unanswered: calculate number of started months

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 ?
Last edited by Wim; 04-26-06 at 21:08.

2. Registered User
Join Date
Apr 2006
Posts
1

## Try this

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

3. Registered User
Join Date
Nov 2004
Posts
1,428
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

4. :-)
Join Date
Jun 2003
Location
Posts
5,516
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".

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•