这是我的公式：
(DATEDIFF('CUSTTRANS-SGCUSTDEBTSETTLEMENT'[TRANSDATE];TODAY();DAY))
现在我想有一个公式，我可以显示一段时间的时间差，比如1岁或1，5岁。
snz8szmq1#
您可以创建一个函数来重用它。这包括所有可能的单位
CREATE FUNCTION ConvertTimeToPeriodFormat ( @FROM_DATE DATETIME, @TO_DATE DATETIME ) RETURNS VARCHAR(100) AS BEGIN DECLARE @Date AS VARCHAR(100) SELECT @Date = CASE WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 1 THEN'1 min old' WHEN DATEDIFF(mi, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mi, @FROM_DATE, @TO_DATE) <= 60 THEN CONVERT(VARCHAR, DATEDIFF(mi, @FROM_DATE, @TO_DATE)) + ' mins old'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 1 THEN CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + ' hour old'WHEN DATEDIFF(hh, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(hh, @FROM_DATE, @TO_DATE) <= 24 THEN CONVERT(VARCHAR, DATEDIFF(hh, @FROM_DATE, @TO_DATE)) + ' hrs old'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 1 THEN CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + ' day old'WHEN DATEDIFF(dd, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(dd, @FROM_DATE, @TO_DATE) <= 7 THEN CONVERT(VARCHAR, DATEDIFF(dd, @FROM_DATE, @TO_DATE)) + ' days old'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 1 THEN CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + ' week old'WHEN DATEDIFF(ww, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(ww, @FROM_DATE, @TO_DATE) <= 4 THEN CONVERT(VARCHAR, DATEDIFF(ww, @FROM_DATE, @TO_DATE)) + ' weeks old'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 1 THEN CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + ' month old'WHEN DATEDIFF(mm, @FROM_DATE, @TO_DATE) > 1 AND DATEDIFF(mm, @FROM_DATE, @TO_DATE) <= 12 THEN CONVERT(VARCHAR, DATEDIFF(mm, @FROM_DATE, @TO_DATE)) + ' months old'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) <= 1 THEN CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + ' year old'WHEN DATEDIFF(yy, @FROM_DATE, @TO_DATE) > 1 THEN CONVERT(VARCHAR, DATEDIFF(yy, @FROM_DATE, @TO_DATE)) + ' years old'END RETURN @Date END
你可以把它当作
select dbo.ConvertTimeToPeriodFormat('YourDateHere',GETDATE()) [Date]
1条答案
按热度按时间snz8szmq1#
您可以创建一个函数来重用它。这包括所有可能的单位
你可以把它当作