时间戳

首页 > 解决方案 > 一段时间的时间公式

sql - 一段时间的时间公式

问题描述

这是我的公式：

(DATEDIFF('CUSTTRANS-SGCUSTDEBTSETTLEMENT'[TRANSDATE];TODAY();DAY))

现在我想有一个公式可以显示一段时间内的时差，比如 1 岁或 1.5 岁。

标签： sqlsql-serverpowerbi

解决方案

您可以创建一个函数来重用它。这涵盖了所有可能的单位

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]

推荐阅读