如何使用 DATEDIFF 返回年月日? [英] How to use DATEDIFF to return year, month and day?
本文介绍了如何使用 DATEDIFF 返回年月日?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在SQL Server 2005
DATEDIFF
返回两个日期的年月日差值DATEDIFF(日期,日期)
结果如何:2年3个月10天
谁能完成这个t-sql
?
ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))返回 VARCHAR(50) AS开始声明@yy INT声明@mm INT声明@getmm INT声明@dd 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))返回 (Convert(varchar(10),@yy) + ‘year’ + Convert(varchar(10),@mm) + ‘month’ + Convert(varchar(10),@dd) + ‘day’)结尾
解决方案
这是我对 Eric 函数的解决方案:
声明@getmm INT声明@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, DATEADD(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))返回 (Convert(varchar(10),@yy) + ‘year’ + Convert(varchar(10),@getmm) + ‘month’ + Convert(varchar(10),@getdd) + ‘day’)
如果开始日期晚于结束日期,最好使用 ABS 进行处理.
<小时>这个:
WITH ex_table AS (选择'2007-01-01''生日时间','2009-03-29' '访问日期时间')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)) +'月'+CAST(DATEDIFF(dd, DATEADD(mm, DATEADD(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 结果FROM ex_table t
.. 或用于 SQL Server 2000 及更早版本的非 CTE:
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)) +'月'+CAST(DATEDIFF(dd, DATEADD(mm, DATEADD(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 结果FROM (SELECT '2007-01-01' '生日时间','2009-03-29' 'visitdatetime') t
...将返回:
结果---------------2年2月28日
参考:DATEDIFF>
How can I use DATEDIFF
to return the difference between two dates in years, months and days in SQL Server 2005
DATEDIFF (date , date)
How to result that: 2 year 3 month 10 day
Can anyone complete this t-sql
?
ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
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 INT
DECLARE @getdd 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))
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 day
Reference: DATEDIFF
这篇关于如何使用 DATEDIFF 返回年月日?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文