显示“时间前日期”的SQL查询像“一周前”,“两周前”,“一个月前”,“一年前”等 [英] 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:
过去7天内的日期 - >一周前
日期过去7天到14天 - >两周前
等...
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…
过去30天内的日子 - >一个月以前
在过去30到60天之间的日期 - >两个月前
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..
过去的日期365天 - >一年前
过去365天到730天的日期 - >两年前
等...
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.
谢谢
推荐答案
如上所述,在SQL查询中使用case语句。这样的一个例子:
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
有关MS SQL的更多信息: http://msdn.microsoft.com/en-us/library/ms181765.aspx
(或查看您的SQL服务器品牌的文档)
More Information for MS SQL: http://msdn.microsoft.com/en-us/library/ms181765.aspx (Or see the documentation for your SQL server brand)
这篇关于显示“时间前日期”的SQL查询像“一周前”,“两周前”,“一个月前”,“一年前”等的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!