我需要一个以下列格式显示日期的查询:
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的更多信息: msdn.microsoft/en-us/library/ms181765.aspx (或查看您的SQL服务器品牌的文档)
More Information for MS SQL: msdn.microsoft/en-us/library/ms181765.aspx (Or see the documentation for your SQL server brand)
更多推荐
显示“时间前日期”的SQL查询像“一周前”,“两周前”,“一个月前”,“一年前”等
发布评论