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
Here is a mysql function I wrote called time_ago
It is used like this
And the result looks like this:
EDIT:
I have modified this answer to offer an improved version of this function: the new version uses DECLARE instead of setting session variables.
As stated above, use a case statement in your SQL query. Something like this:
More Information for MS SQL: http://msdn.microsoft.com/en-us/library/ms181765.aspx (Or see the documentation for your SQL server brand)