如何使用 DATEDIFF 返回年月日?

How can I use DATEDIFF to return the difference between two dates in years, months and days in SQL Server 2005DATEDIFF (date , date)How to result that: 2 year 3 month 10 dayCan anyone complete this t-sql?ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))RETURNS VARCHAR(50) ASBEGIN DECLARE @yy INT DECLARE @mm INT DECLARE @getmm INT DECLARE @dd INT SET @yy = DATEDIFF(yy, @dstart, @dend) SET @mm = DATEDIFF(mm, @dstart, @dend) SET @dd = DATEDIFF(dd, @dstart, @dend) SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend)) RETURN ( Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@mm) + 'month' + Convert(varchar(10),@dd) + 'day' )END 解决方案 Here's my solution to Eric's function:DECLARE @getmm INTDECLARE @getdd INTSET @yy = DATEDIFF(yy, @dstart, @dend)SET @mm = DATEDIFF(mm, @dstart, @dend)SET @dd = DATEDIFF(dd, @dstart, @dend)SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))RETURN ( Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@getmm) + 'month' + Convert(varchar(10),@getdd) + 'day')Good call on the use of ABS to handle if the start date is after the end date.This:WITH ex_table AS ( SELECT '2007-01-01' 'birthdatetime', '2009-03-29' 'visitdatetime')SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+ CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+ CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result FROM ex_table t..or non-CTE using for SQL Server 2000 and prior:SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+ CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+ CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result FROM (SELECT '2007-01-01' 'birthdatetime', '2009-03-29' 'visitdatetime') t...will return:result----------------------2 year 2 month 28 dayReference: DATEDIFF 这篇关于如何使用 DATEDIFF 返回年月日?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！