DATEDIFF in months < 0 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DATEDIFF in months < 0

Author

Topic

brettrg
Starting Member

7 Posts

Posted - 2005-08-08 : 14:07:43

I have two dates. I am trying to get a count of the number of months between the two dates. I want the returned value to meet the following:

1) never be 0
2) 1/1/2000 - 1/31/2000 should be 1, not 0
3) 1/1/2000 - 1/15/2000 should be .5, not 0 or 1
5) 1/1/2000 - 2/15/2000 should be 1.5

So basically i just want clean results that give me a decimal value to one place.

I've tried taking the datediff in days divided by 30 and then converting that to a decimal(3,1) but anything less than 1 just gives me zero.

convert(decimal(3,1),(DATEDIFF(d,date1,date2))/30)

Any help would be much appreciated.

Thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-08 : 15:32:07

you just needed to set your denominator to something more precise than int. Is this the results you need though?


set nocount on
declare @tb table (rowid int identity(1,1), date1 datetime, date2 datetime)

declare @i int
set @i = 1
while @i < 91
begin
	insert @tb (date1, date2) 
	values ('1/1/2000', dateadd(day,@i,'1/1/2000'))
	set @i = @i+1
end

select	date1 = convert(varchar,date1,101)
	,date2 = convert(varchar,date2,101)
	,diff = convert(decimal(3,1),(DATEDIFF(d,date1,date2))/ 30.00)
from	@tb
order by rowid

Be One with the Optimizer
TG

brettrg
Starting Member

7 Posts

Posted - 2005-08-08 : 17:57:46

Thanks...that works!!

Much appreciated

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-08 : 20:00:53

That's a tricky problem to get right. For example, most people would consider the difference between 2005/2/1 and 2005/3/1 to be a full month, but your algorithim would return .9:

select
	[Months] = convert(decimal(6,1),round(datediff(dd,d1,d2)/30.00,1))
from
	(select d1 = '2005/2/1' ,d2 = '2005/3/1' ) a

Months   
-------- 
.9

(1 row(s) affected)

The real problem you have is that months vary in the number of days, so there is no common understanding to the concept of days per month.

CODO ERGO SUM

TimS
Posting Yak Master

198 Posts

Posted - 2005-08-08 : 20:21:55

select
[Months] = convert(decimal(6,1),round(
datediff(dd,d1,d2)/ CAST(datediff(dd,d1,DATEADD ( mm , 1, d1 )) AS decimal(6,1))
,1))
from
(
select '2005/2/1' ,'2005/3/1' UNION ALL
select '2005/2/1' ,'2005/2/15'
) a (d1, d2)

Tim S -- The above is the result of trial an error no idea if it works for any cases except the two shown.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-08 : 22:04:41

quote:
Tim S -- The above is the result of trial an error no idea if it works for any cases except the two shown.

select '2004/12/31' ,'2005/12/31'
Months
--------
11.8

This: a (d1, d2)
Was really nice, long time no see.

Edit:
Further complications if the time interval spans > 1 month.
Brettrg, can You define the calculation algorithm?

rockmoose

TimS
Posting Yak Master

198 Posts

Posted - 2005-08-08 : 22:21:30

SELECT d1,d2,
[Months] = convert(decimal(6,1),round(
datediff(mm,d1,d2) +
(DAY ( d2 ) - DAY ( d1 ))/CAST(datediff(dd,d1,DATEADD ( mm , 1, d1 )) AS decimal(6,1))
,1))
from
(
select '2005/2/1' ,'2005/3/1' UNION ALL
select '2005/2/1' ,'2005/2/15' UNION ALL
select '2004/12/31' ,'2005/12/31' UNION ALL
select '1/1/2000' ,'1/31/2000' UNION ALL -- should be 1, not 0
select '1/1/2000' ,'1/15/2000' UNION ALL -- should be .5, not 0 or 1
select '1/1/2000' ,'2/15/2000' -- UNION ALL -- should be 1.5
) a (d1, d2)

Tim S

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-08 : 22:28:28

That was some piece of

sql !!!

rockmoose

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-09 : 08:44:43

declare @d1 smalldatetime, @d2 smalldatetime
set @d1 = '2005-02-11'
set @d2 = '2005-03-29'

select round(convert(float, datediff(d,@d1,@d2))/30,0)

Kapil Arya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources