Search code examples
sqltimedatetimeagoformatdatetime

SQL query that displays "time ago dates" like “one week ago”, “two weeks ago”, “one month ago”, “one year ago”, etc

I need a query that displays dates in the following format:

Dates that fall in the past 7 days -> “one week ago” Dates that fall in the past 7 to 14 days -> “two week ago” Etc…

Dates that fall in the past 30 days -> “one month ago” Dates that follow in the past 30 to 60 days -> “two months ago Etc..

Dates that fall in the past 365 days -> “one year ago” Dates that fall in the past 365 to 730 days -> “two years ago Etc...

If you guys can point me to the right direction I’ll appreciate it.

Thank you

Solution

  • As stated above, use a case statement in your SQL query. Something like this:

    SELECT 
Column1, 
Column2, 
theDate,
CASE
  WHEN DATEDIFF(dd, theDate, GetDate()) =< 7 THEN 'One Week Ago'
  WHEN DATEDIFF(dd, theDate, GetDate()) > 7 AND DATEDIFF(dd, theDate, GetDate()) < 30 THEN 'One Month Ago'
  -- ...
  END
AS TimeAgo,
Column3,
Column4
FROM Table1

    More Information for MS SQL: http://msdn.microsoft.com/en-us/library/ms181765.aspx (Or see the documentation for your SQL server brand)