sql server - datediff rounding

Question

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 run, 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

Answer 1

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