DATEDIFF(月)
问题描述:
我有则DateDiff(月的两个日期之间的数)的问题DATEDIFF(月)
当我试试这个:
SELECT
(CASE
WHEN X IS NOT NULL THEN
CONVERT(varchar(4), DATEDIFF(YEAR, Y, C)) + ' years '
ELSE
CONVERT(varchar(4), DATEDIFF(YEAR, A, GETDATE())) + ' years '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy,GETDATE(),b), S), GETDATE()) AS varchar(4)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, c, GETDATE()), e), GETDATE()), DATEADD(yy, DATEDIFF(yy, f, GETDATE()), g)), GETDATE()) AS varchar(2)) +' day' end) as result
FROM g INNER JOIN k ON M=U
WHERE T=XX order by Y;
结果是这样的:2年342个月2天
但它应该是这样的:2年3个月2日前例如,
有人可以请指出我在正确的方向。谢谢。
正确答案:
CONVERT(varchar(4), DATEDIFF(dd, x, GETDATE())/365) + ' år '+
CONVERT(varchar(4), DATEDIFF(MONTH, x, GETDATE()) % 12) + ' måneder '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, x, GETDATE()), x), GETDATE()),
DATEADD(yy, DATEDIFF(yy, x, GETDATE()), x)), GETDATE()) AS varchar(2)) +' dag ' end) as Y,
答
你需要做一些额外的计算。看看从这里采取了这种
declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = GETDATE()
set @mdob = '2014-12-22'
Select
"Years" = CASE
WHEN (DATEPART(DAY , @mdob)) > (DATEPART(DAY , @AsOnDate))
and (DATEPART(Month,@mdob)) = (DATEPART(Month, @AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)/12)
ELSE (datediff(month,@mdob,@AsOnDate)/12)
END,
"Months" = CASE
WHEN (DATEPART(DAY , @mdob)) > (DATEPART(DAY , @AsOnDate))
and (DATEPART(Month,@mdob)) = (DATEPART(Month,@AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)% 12)
ELSE (datediff(month,@mdob,@AsOnDate)% 12)
END
解决方案: https://www.sqlservercentral.com/Forums/Dialogs/PostDetails.aspx?PostID=1423756
请编辑您的问题,并提供样本数据和预期的结果。 –
[如何使用DATEDIFF返回年,月和日?](http://*.com/questions/1541570/how-to-use-datediff-to-return-year-month-and-day ) – Tanner
@GordonLinoff;我做了编辑,如果还有其他东西,请告诉我:) – RyaN