title: Best way to convert DateTime to "n Hours Ago" in SQL
body: <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>