<p>I wrote a SQL function to convert a datetime value in SQL to a friendlier "n Hours Ago" or "n Days Ago" etc type of message. And I was wondering if there was a better way to do it. </p>

<p>(Yes I know "don't do it in SQL" but for design reasons I have to do it this way).</p>

<p>Here is the function I've written:</p>

<pre><code>CREATE FUNCTION dbo.GetFriendlyDateTimeValue
(
    @CompareDate DateTime
)
RETURNS nvarchar(48)
AS
BEGIN
    DECLARE @Now DateTime
    DECLARE @Hours int
        DECLARE @Suff nvarchar(256)
    DECLARE @Found bit

    SET @Found = 0
    SET @Now = getDate()
    SET @Hours = DATEDIFF(MI, @CompareDate, @Now)/60

    IF @Hours &lt;= 1
    BEGIN
        SET @Suff = 'Just Now'
        SET @Found = 1
        RETURN @Suff
    END

    IF @Hours &lt; 24
    BEGIN
        SET @Suff = ' Hours Ago'
        SET @Found = 1
    END

    IF @Hours &gt;= 8760 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 8760
        SET @Suff = ' Years Ago'
        SET @Found = 1
    END

    IF @Hours &gt;= 720 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 720
        SET @Suff = ' Months Ago'
        SET @Found = 1
    END

    IF @Hours &gt;= 168 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 168
        SET @Suff = ' Weeks Ago'
        SET @Found = 1
    END

    IF @Hours &gt;= 24 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 24
        SET @Suff = ' Days Ago'
        SET @Found = 1
    END

    RETURN Convert(nvarchar, @Hours) + @Suff
END
</code></pre>

[{'answer_id': 50192, 'author': 'Craig', 'author_id': 2894, 'author_profile': 'https://Stackoverflow.com/users/2894', 'pm_score': 1, 'selected': False, 'text': '<p>Your code looks functional.  As for a better way, that is going to get subjective.  You might want to check out this <a href="http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx" rel="nofollow noreferrer">page</a> as it deals with time spans in SQL. </p>\n'}, {'answer_id': 50193, 'author': 'ConroyP', 'author_id': 2287, 'author_profile': 'https://Stackoverflow.com/users/2287', 'pm_score': 4, 'selected': True, 'text': '<p>As you say, I probably wouldn\'t do it in SQL, but as a thought exercise have a MySQL implementation:</p>\n\n<pre><code>CASE\n    WHEN compare_date between date_sub(now(), INTERVAL 60 minute) and now() \n        THEN concat(minute(TIMEDIFF(now(), compare_date)), \' minutes ago\')\n\n    WHEN datediff(now(), compare_date) = 1 \n        THEN \'Yesterday\'\n\n    WHEN compare_date between date_sub(now(), INTERVAL 24 hour) and now() \n        THEN concat(hour(TIMEDIFF(NOW(), compare_date)), \' hours ago\')\n\n    ELSE concat(datediff(now(), compare_date),\' days ago\')\nEND\n</code></pre>\n\n<p>Based on a similar sample seen on the <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#c9500" rel="noreferrer">MySQL Date and Time</a> manual pages</p>\n'}, {'answer_id': 50228, 'author': 'JosephStyons', 'author_id': 672, 'author_profile': 'https://Stackoverflow.com/users/672', 'pm_score': 2, 'selected': False, 'text': "<p>In Oracle:</p>\n\n<pre><code>select\n  CC.MOD_DATETIME,\n  'Last modified ' ||\n  case when (sysdate - cc.mod_datetime) &lt; 1\n       then round((sysdate - CC.MOD_DATETIME)*24) || ' hours ago'\n       when (sysdate - CC.MOD_DATETIME) between 1 and 7\n       then round(sysdate-CC.MOD_DATETIME) || ' days ago'\n       when (sysdate - CC.MOD_DATETIME) between 8 and 365\n       then round((sysdate - CC.MOD_DATETIME) / 7) || ' weeks ago'\n       when (sysdate - CC.MOD_DATETIME) &gt; 365   \n       then round((sysdate - CC.MOD_DATETIME) / 365) || ' years ago'\n       end\nfrom \n  customer_catalog CC\n</code></pre>\n"}, {'answer_id': 50301, 'author': 'Jasmine', 'author_id': 5255, 'author_profile': 'https://Stackoverflow.com/users/5255', 'pm_score': 1, 'selected': False, 'text': '<p>How about this? You could expand this pattern to do "years" messages, and you could put in a check for "1 day" or "1 hour" so it wouldn\'t say "1 days ago"...</p>\n\n<p>I like the CASE statement in SQL.</p>\n\n<pre><code>drop function dbo.time_diff_message    \nGO\n\ncreate function dbo.time_diff_message (\n    @input_date datetime\n)\nreturns varchar(200)    \nas    \nbegin    \ndeclare @msg varchar(200)    \ndeclare @hourdiff int\n\nset @hourdiff = datediff(hour, @input_date, getdate())    \nset @msg = case when @hourdiff &lt; 0 then \' from now\' else \' ago\' end    \nset @hourdiff = abs(@hourdiff)    \nset @msg = case when @hourdiff &gt; 24 then convert(varchar, @hourdiff/24) + \' days\' + @msg\n                else convert(varchar, @hourdiff) + \' hours\' + @msg\n            end\n\nreturn @msg\nend\n\nGO    \nselect dbo.time_diff_message(\'Dec 7 1941\')\n</code></pre>\n'}, {'answer_id': 50341, 'author': 'Hafthor', 'author_id': 4489, 'author_profile': 'https://Stackoverflow.com/users/4489', 'pm_score': 2, 'selected': False, 'text': "<p>My attempt - this is for MS SQL. It supports 'ago' and 'from now', pluralization and it doesn't use rounding or datediff, but truncation -- datediff gives 1 month diff between 8/30 and 9/1 which is probably not what you want. Rounding gives 1 month diff between 9/1 and 9/16. Again, probably not what you want.</p>\n\n<pre><code>CREATE FUNCTION dbo.GetFriendlyDateTimeValue( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN\ndeclare @s nvarchar(48)\nset @s='Now'\nselect top 1 @s=convert(nvarchar,abs(n))+' '+s+case when abs(n)&gt;1 then 's' else '' end+case when n&gt;0 then ' ago' else ' from now' end from (\n select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (\n  select 1/365 as n, 'Year' as s union all\n  select 1/30, 'Month' union all\n  select 1, 'Day' union all\n  select 7, 'Week' union all\n  select 24, 'Hour' union all\n  select 24*60, 'Minute' union all\n  select 24*60*60, 'Second'\n ) k\n) j where abs(n)&gt;0 order by abs(n)\nreturn @s\nEND\n</code></pre>\n"}, {'answer_id': 1453961, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': "<p>Thanks for the various code posted above.</p>\n\n<p>As Hafthor pointed out there are limitations of the original code to do with rounding. I also found that some of the results his code kicked out didn't match with what I'd expect e.g. Friday afternoon -> Monday morning would show as '2 days ago'. I think we'd all call that 3 days ago, even though 3 complete 24 hour periods haven't elapsed.</p>\n\n<p>So I've amended the code (this is MS SQL). Disclaimer: I am a novice TSQL coder so this is quite hacky, but works!!</p>\n\n<p>I've done some overrides - e.g. anything up to 2 weeks is expressed in days. Anything over that up to 2 months is expressed in weeks. Anything over that is in months etc. Just seemed like the intuitive way to express it.</p>\n\n<pre><code>CREATE FUNCTION [dbo].[GetFriendlyDateTimeValue]( @CompareDate DATETIME ) RETURNS NVARCHAR(48) AS BEGIN\ndeclare @s nvarchar(48)\n\nset @s='Now'\nselect top 1 @s=convert(nvarchar,abs(n))+' '+s+case when abs(n)&gt;1 then 's' else '' end+case when n&gt;0 then ' ago' else ' from now' end from (\n select convert(int,(convert(float,(getdate()-@comparedate))*n)) as n, s from (\n  select 1/365 as n, 'year' as s union all\n  select 1/30, 'month' union all\n  select 1/7, 'week' union all\n  select 1, 'day' union all\n  select 24, 'hour' union all\n  select 24*60, 'minute' union all\n  select 24*60*60, 'second'\n ) k\n) j where abs(n)&gt;0 order by abs(n)\n\nif @s like '%days%'\nBEGIN\n -- if over 2 months ago then express in months\n IF convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) &gt;= 2\n BEGIN\n  select @s = convert(nvarchar,DATEDIFF(MM, @CompareDate, GETDATE())) + ' months ago'\n END\n\n -- if over 2 weeks ago then express in weeks, otherwise express as days\n ELSE IF convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) &gt;= 14\n BEGIN\n  select @s = convert(nvarchar,DATEDIFF(WK, @CompareDate, GETDATE())) + ' weeks ago'\n END\n\n ELSE\n  select @s = convert(nvarchar,DATEDIFF(DD, @CompareDate, GETDATE())) + ' days ago'\nEND\n\nreturn @s\nEND\n</code></pre>\n"}, {'answer_id': 32572897, 'author': 'Eric Bynum', 'author_id': 4465532, 'author_profile': 'https://Stackoverflow.com/users/4465532', 'pm_score': 0, 'selected': False, 'text': "<p>The posts above gave me some good ideas so here is another function for anyone using SQL Server 2012.</p>\n\n<pre><code>    CREATE FUNCTION [dbo].[FN_TIME_ELAPSED] \n    (\n        @TIMESTAMP DATETIME\n    )\n    RETURNS VARCHAR(50)\n    AS\n    BEGIN\n\n    RETURN \n    (\n        SELECT TIME_ELAPSED = \n            CASE\n                WHEN @TIMESTAMP IS NULL THEN NULL\n                WHEN MINUTES_AGO &lt; 60 THEN CONCAT(MINUTES_AGO, ' minutes ago')\n                WHEN HOURS_AGO &lt; 24 THEN CONCAT(HOURS_AGO, ' hours ago')\n                WHEN DAYS_AGO &lt; 365 THEN CONCAT(DAYS_AGO, ' days ago')\n                ELSE CONCAT(YEARS_AGO, ' years ago') END\n        FROM ( SELECT MINUTES_AGO = DATEDIFF(MINUTE, @TIMESTAMP, GETDATE()) ) TIMESPAN_MIN\n        CROSS APPLY ( SELECT HOURS_AGO = DATEDIFF(HOUR, @TIMESTAMP, GETDATE()) ) TIMESPAN_HOUR\n        CROSS APPLY ( SELECT DAYS_AGO = DATEDIFF(DAY, @TIMESTAMP, GETDATE()) ) TIMESPAN_DAY\n        CROSS APPLY ( SELECT YEARS_AGO = DATEDIFF(YEAR, @TIMESTAMP, GETDATE()) ) TIMESPAN_YEAR\n    )\n    END\n    GO\n</code></pre>\n\n<p>And the implementation:</p>\n\n<pre><code>    SELECT TIME_ELAPSED = DBO.FN_TIME_ELAPSED(AUDIT_TIMESTAMP)\n    FROM SOME_AUDIT_TABLE\n</code></pre>\n"}, {'answer_id': 69703438, 'author': 'Nitin Luhar', 'author_id': 9325508, 'author_profile': 'https://Stackoverflow.com/users/9325508', 'pm_score': 0, 'selected': False, 'text': "<pre><code>CASE WHEN datediff(SECOND,OM.OrderDate,GETDATE()) &lt; 60 THEN\n  CONVERT(NVARCHAR(MAX),datediff(SECOND,OM.OrderDate,GETDATE())) +' seconds ago'\nWHEN datediff(MINUTE,OM.OrderDate,GETDATE()) &lt; 60 THEN\n  CONVERT(NVARCHAR(MAX),datediff(MINUTE,OM.OrderDate,GETDATE())) +' minutes ago'\nWHEN datediff(HOUR,OM.OrderDate,GETDATE()) &lt; 24 THEN\n  CONVERT(NVARCHAR(MAX),datediff(HOUR,OM.OrderDate,GETDATE())) +' hours ago'\nWHEN datediff(DAY,OM.OrderDate,GETDATE()) &lt; 8 THEN\n  CONVERT(NVARCHAR(MAX),datediff(DAY,OM.OrderDate,GETDATE())) +' Days ago'\nELSE FORMAT(OM.OrderDate,'dd/MM/yyyy hh:mm tt') END AS TimeStamp\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/50149', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1980/']