SQL Server Function to Get How Long in Word

This below SQL User Defined Function script is to returns How Long the date already passed, in wording. It's usually displayed in articles to inform the reader how long the article already posted.


The basic idea is just comparing 2 dates --- specific date and current date / GETDATE() ---, and returns in a simple wording.  The longest time is YEAR, while the fastest time is will return "JUST NOW" if the different is within 1 hour.

1. CREATE FUNCTION [dbo].[uf_WasInWord]   
2. (@dtDate DATETIME)  
3. RETURNS VARCHAR(255)  
4. AS  
5. BEGIN  
6.     DECLARE @sReturns VARCHAR(255) = ''  
7.     DECLARE @iHours INT = 0  
8.   
9.     SET @iHours = DATEDIFF(HOUR, @dtDate, GETDATE())  
10.     IF @iHours / 8766 > 0  
11.         SET @sReturns = CONVERT(VARCHAR(10),@iHours / 8766) + ' year(s) ago'  
12.     ELSE IF DATEDIFF(DAY, @dtDate, GETDATE()) >= 30  
13.         SET @sReturns = CONVERT(VARCHAR(10),DATEDIFF(MONTH, @dtDate, GETDATE())) + ' month(s) ago'  
14.     ELSE IF DATEDIFF(DAY, @dtDate, GETDATE()) > 6  
15.         SET @sReturns = CONVERT(VARCHAR(10),DATEDIFF(DAY, @dtDate, GETDATE()) / 7) + ' week(s) ago'  
16.     ELSE IF DATEDIFF(DAY, @dtDate, GETDATE()) > 2  
17.         SET @sReturns = CONVERT(VARCHAR(10),DATEDIFF(DAY, @dtDate, GETDATE())) + ' day(s) ago'  
18.     ELSE IF DATEDIFF(DAY, @dtDate, GETDATE()) >= 1  
19.         SET @sReturns = 'Yesterday'  
20.     ELSE IF DATEDIFF(HOUR, @dtDate, GETDATE()) > 1  
21.         SET @sReturns = DATEDIFF(HOUR, @dtDate, GETDATE()) + ' hour(s) ago'  
22.     ELSE  
23.         SET @sReturns = 'Just now'  
24.   
25.     RETURN @sReturns  
26. END  

To use it, you can call the function directly from SQL Select like this example below:

1. SELECT dbo.uf_WasInWord(created_date) FROM tips