datediff rounding
- by derekcohen
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
