'2年-2个月21天'为什么我在减去月份时会减去 - DATEDIFF [英] '2 year -2 month 21 day' why I get minus , when returning month - DATEDIFF

问题描述

当我执行我的查询时，我得到一个月减去！有人可以指出我正确的方向。谢谢。

When i executed my Query , i got a minus with month ! Can someone please point me in the right direction. Thanks.

SELECT 
CAST(DATEDIFF(yy, WorkStartDate, GETDATE()) AS varchar(4)) +' year '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()) AS varchar(2)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day'   as result
FROM dbo.Paychecks INNER JOIN dbo.Users ON Users.Id=Paychecks.UserId
WHERE Users.CustomerId=214 order by Users.FirstName;

我尝试过：

What I have tried:

SELECT 
CAST(DATEDIFF(yy, WorkStartDate, GETDATE()) AS varchar(4)) +' year '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()) AS varchar(2)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate), GETDATE()), DATEADD(yy, DATEDIFF(yy, WorkStartDate, GETDATE()), WorkStartDate)), GETDATE()) AS varchar(2)) +' day'   as result
FROM dbo.Paychecks INNER JOIN dbo.Users ON Users.Id=Paychecks.UserId
WHERE Users.CustomerId=214 order by Users.FirstName;

推荐答案

如果减法左边的日期小于右边的日期，那么结果将为负数。旧日期的价值低于较新的日期。

If the date on the left of the subtraction is less than the date on the right, then the result will be negative. Older dates are less in value to newer dates.

这篇关于'2年-2个月21天'为什么我在减去月份时会减去 - DATEDIFF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！