datediff rounding

I have a db table in SQL Server which contains a start date for a project.

On a web status page I want to show how many days/weeks/months the project has r开发者_运维知识库un, the units depending on the duration. So under 21 days I'd show days, under 7 weeks I'd show weeks, otherwise show completed months. So I get the days, weeks and months values and can then use some code to decide which one to display.

Suppose the project starts on 30 Dec 2010 and I'm checking today (27 Feb 2011).

select datediff(d,'30 Dec 2010',getdate()) as days, 
datediff(wk,'30 Dec 2010',getdate()) as weeks , 
datediff(m,'30 Dec 2010',getdate())as months

produces

days: 59    weeks: 9    months: 2

But in fact the difference is 8 whole weeks and some rounding takes place.

I've tried doing it in ASP as well, getting the start date and then doing the datediff() but it's no better.

Is there a better way?

thanks

DATEDIFF uses the boundary of the date part. So for "day", 23:59 today is one whole day before 00:01 tomorrow.

These kind calculations are best done using the same unit.

;WITH cDayDiff AS
(
   select datediff(day,'20101230',getdate()) as days
)
SELECT
   days / 7 as weeks, --integer division
   days % 7 as remainingdays
FROM
   cDayDiff

Months is tricker because each month varies. But you can compare the day numbers (per month) to correct the value.

SELECT
  datediff(month, '20101230', getdate()) -
     CASE WHEN DATEPART(day, '20101230') > DATEPART(day, getdate()) THEN 1 ELSE 0 END 
 as months

select datediff(d,'30 Dec 2010',getdate()) as days,
datediff(d,'30 Dec 2010',getdate())/7 as weeks

This gives you 8 weeks. You can divide by 7.0 to get decimals. In this case you get 8.428571

If you want an actual calendar reckoning of days, weeks and months, I think your best bet is to use NodaTime in a custom CLR SQL Server function.

NodaTime is a .NET port of the Java library JodaTime. JodaTime is very comprehensive; it supports eight different calendar systems, and will even account for leap years.

Here is a short introduction to JodaTime:
http://technology.amis.nl/blog/5604/introducing-joda-time-the-smart-date-api

Probably you are going to copy/paste code to round DATEDIFF.

BUT WAIT A MINUTE!!!

If you need something like this:

IF DATEDIFF(MONTH, @dateInPast, @today) > @monthes

I would suggest to use something like this:

IF @today > DATEADD(MONTH, @monthes, @dateInPast)