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