日期差异舍入 [英] datediff rounding

问题描述

我在 SQL Server 中有一个 db 表，其中包含项目的开始日期.

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

在网络状态页面上，我想显示项目运行了多少天/周/月，单位取决于持续时间.所以在 21 天之内我会显示天数，在 7 周内我会显示周数，否则显示已完成的月份.所以我得到了天、周和月的值，然后可以使用一些代码来决定要显示哪一个.

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.

假设该项目于 2010 年 12 月 30 日开始，我今天(2011 年 2 月 27 日)进行检查.

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

生产

days: 59    weeks: 9    months: 2

但实际上差异是整整 8 周，并且进行了一些四舍五入.

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

我也尝试过在 ASP 中执行此操作，获取开始日期，然后执行 datediff() 但也没有更好.

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

有没有更好的方法?

谢谢

推荐答案

DATEDIFF 使用日期部分的边界.因此，对于一天"，今天的 23:59 是明天 00:01 之前的一整天.

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

这篇关于日期差异舍入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！