quote:

---

Originally posted by mr_mist

How are you determining what a complete month is? I'm not sure why the first one is a complete month but the second is not?

Can you use datediff with a number of days?

-------
Moo. :)

---

quote:

---

Originally posted by spirit1

it could probably be very simplified, but this is the first thing that comes to mind

if day(@startDate) = day(@EndDate)-1 and month(@startDate) = month(@EndDate)-1 and
(year(@startDate) = year(@EndDate) or year(@startDate) = year(@EndDate)-1) then
return 1
else
return 0

Go with the flow & have fun! Else fight the flow

---

quote:

---

Originally posted by Amethystium
...Example :
04/02/2004 to 03/03/2004 is one complete month whereas 04/02/2004 to 02/03/2004 is not. The start date is where the month count should begin and I am only interested in complete months...

---

quote:

---

Originally posted by spirit1

i think he means that the "month" is defined by:
day N, month M
to
day N-1, month M+1

so day N, month M+1 is alread in the next month.


Go with the flow & have fun! Else fight the flow

---

declare @startdate datetime, @EndDate datetime
--select 	@startdate = '01/02/2003', @EndDate = '22/10/2003'
select 	@startdate = '02/02/2003', @EndDate = '22/10/2003'

select 	datediff(m, @startdate, @EndDate) as IncorrectDiff, 
	case when day(@startdate) = 1 then datediff(m, @startdate, @EndDate) else datediff(m, @startdate, @EndDate)-1 end as CorrectDiff

quote:

---

Originally posted by jsmith8858

what is the answer? 8? or a list of start/end dates within that time period? What do you want returned?

- Jeff

---

quote:

---

Originally posted by Amethystium
...Yes I want the number of complete months.

Spirit, thanks for the query but it returns 0 when I input 04/02/2003 and 03/03/2003 even though it is a complete month (note that both end dates should be included)....

---

quote:

---

Originally posted by Michael Valentine Jones

I have read you posts several times, and I have to say that you have not made it clear what your definition of a "complete month" is and what output you are expecting to see. Until you do that, it will difficult for anyone to help you.

quote:

---

Originally posted by Amethystium
...Yes I want the number of complete months.

Spirit, thanks for the query but it returns 0 when I input 04/02/2003 and 03/03/2003 even though it is a complete month (note that both end dates should be included)....

---

CODO ERGO SUM

---

quote:

---

Originally posted by Michael Valentine Jones

I have read you posts several times, and I have to say that you have not made it clear what your definition of a "complete month" is and what output you are expecting to see. Until you do that, it will difficult for anyone to help you.

quote:

---

Originally posted by Amethystium
...Yes I want the number of complete months.

Spirit, thanks for the query but it returns 0 when I input 04/02/2003 and 03/03/2003 even though it is a complete month (note that both end dates should be included)....

---

CODO ERGO SUM

---

if exists (select * from information_schema.tables where table_name = 'mdays')
drop table dbo.mdays
go

create table dbo.mdays (
	monthNumber	int identity(1, 1),
	monthDays int
	)

insert into dbo.mdays (monthDays)
select 31 union all 
select 28 union all 
select 31 union all 
select 30 union all 
select 31 union all 
select 30 union all 
select 31 union all 
select 31 union all 
select 30 union all 
select 31 union all 
select 30 union all
select 31
go

if exists (select * from sysobjects where name = 'fn_Xndays_in_mth' and type = 'FN')
drop function dbo.fn_Xndays_in_mth
go

create function dbo.fn_Xndays_in_mth (@mth int, @yr int) 
returns smallint as

begin 

declare @OffSet int
declare @theResult int

set @theResult = 0
set @OffSet = 0

if @mth = 2
begin
	if (@yr%4 = 0 and @yr%100 <> 0) or @yr%1000 = 0 
	begin
		set @Offset = 1
	end
	else
		set @Offset = 0
end

set @theResult = (select monthDays from mdays where monthNumber = @mth) + @Offset

return @theResult

end

-- Main function

if exists (select * from sysobjects where name = 'fn_Complete_Months' and type = 'FN')
drop function dbo.fn_Complete_Months
go

create function dbo.fn_Complete_Months (@Dst datetime, @DEnd datetime) 
returns smallint as

begin 

declare @Offset int

set @Offset = 0

if  day(@Dst) = 1 and day(@DEnd) = dbo.fn_Xndays_in_mth(month(@DEnd), year(@DEnd))
begin
	set @Offset = 1
end
else
	if day(@Dst) > day(@DEnd) + 1
	begin
		set @Offset = -1
	end

return (year(@DEnd) - year(@Dst)) + (month(@DEnd) - month(@Dst) + @Offset)

end

select dbo.fn_Complete_Months(convert(datetime, '04/02/2003', 103), convert(datetime, '03/06/2003', 103))

declare @theResult int
declare @mth int,
	 @yr int

select @yr = 2004
select @mth = 2

select @theResult =
	day(dateadd(dd,-1,dateadd(mm,@mth,dateadd(yy,@yr-1900,0))))

select @theResult

declare @dt	datetime
select	 @dt	= '2004/02/29'

select day(dateadd(dd,-1,dateadd(mm,datediff(mm,0,@dt)+1,0)))

declare @Dst datetime, @DEnd datetime
select @Dst	= '2002/12/04'
select @DEnd	= '2003/04/04'

select dbo.fn_Complete_Months(@Dst,@DEnd)

declare @Dst datetime, @DEnd datetime
select @Dst	= '2002/12/04'
select @DEnd	= '2003/04/04'

select Full_Months =
	datediff(mm,dateadd(mm,datediff(mm,0,@Dst),0),
	dateadd(mm,datediff(mm,0,@DEnd),0))-1
	+
	case
	when day(@Dst) = 1 and 
	     day(@DEnd) =
		-- Days in the month
		day(dateadd(dd,-1,dateadd(mm,datediff(mm,0,@DEnd)+1,0)))
	then 2
	when day(@Dst) > day(@DEnd) + 1
	then 0
	else 1
	end

quote:

---

Originally posted by Michael Valentine Jones

You can compute the number of days in a month directly using SQL functions, without using a table:

declare @theResult int
declare @mth int,
	 @yr int

select @yr = 2004
select @mth = 2

select @theResult =
	day(dateadd(dd,-1,dateadd(mm,@mth,dateadd(yy,@yr-1900,0))))

select @theResult

However, there is no reason to extract the Year and Month to get the total days in a month, because you can compute it directly from a datetime value:

declare @dt	datetime
select	 @dt	= '2004/02/29'

select day(dateadd(dd,-1,dateadd(mm,datediff(mm,0,@dt)+1,0)))

Have you really tested your code? For example, the following produced a value of -7 when I ran it. From what I could tell, I believe you would want a value of 4.

declare @Dst datetime, @DEnd datetime
select @Dst	= '2002/12/04'
select @DEnd	= '2003/04/04'

select dbo.fn_Complete_Months(@Dst,@DEnd)

This produced the result I believe you are looking for, 4:

declare @Dst datetime, @DEnd datetime
select @Dst	= '2002/12/04'
select @DEnd	= '2003/04/04'

select Full_Months =
	datediff(mm,dateadd(mm,datediff(mm,0,@Dst),0),
	dateadd(mm,datediff(mm,0,@DEnd),0))-1
	+
	case
	when day(@Dst) = 1 and 
	     day(@DEnd) =
		-- Days in the month
		day(dateadd(dd,-1,dateadd(mm,datediff(mm,0,@DEnd)+1,0)))
	then 2
	when day(@Dst) > day(@DEnd) + 1
	then 0
	else 1
	end

CODO ERGO SUM

---