SQL 如何在sql server中获取从当前日期时间到过去7天的过去7天数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27599557/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get last 7 days data from current datetime to last 7 days in sql server
提问by SRI
Hi I am loading table A data from sql server to mysql using pentaho when loading data i need to get only last 7 days data from sql server A table to mysql In sql server createddate column data type is like datetime AND In mysql created_on column datatype is timestamp
嗨，我正在使用 pentaho 将表 A 数据从 sql server 加载到 mysql时间戳
Here I used below query but i am getting only 5 days data
Please help me in this issue
在这里我使用了下面的查询，但我只得到了 5 天的数据
请帮助我解决这个问题
select id,
NewsHeadline as news_headline,
NewsText as news_text,
state,
CreatedDate as created_on
from News
WHERE CreatedDate BETWEEN GETDATE()-7 AND GETDATE()
order by createddate DESC
回答by SMA
Try something like:
尝试类似：
SELECT id, NewsHeadline as news_headline, NewsText as news_text, state CreatedDate as created_on
FROM News
WHERE CreatedDate >= DATEADD(day,-7, GETDATE())
回答by Dhamodharan Subramanian
select id,
NewsHeadline as news_headline,
NewsText as news_text,
state,
CreatedDate as created_on
from News
WHERE CreatedDate>=DATEADD(DAY,-7,GETDATE())
回答by Raj
I don't think you have data for every single day for the past seven days. Days for which no data exist, will obviously not show up.
我认为您没有过去 7 天每一天的数据。不存在数据的日子显然不会出现。
Try this and validate that you have data for EACH day for the past 7 days
试试这个并验证您是否拥有过去 7 天每一天的数据
SELECT DISTINCT CreatedDate
FROM News
WHERE CreatedDate >= DATEADD(day,-7, GETDATE())
ORDER BY CreatedDate
EDIT - Copied from your comment
编辑 - 从您的评论中复制
i have dec 19th -1 row data,18th -2 rows,17th -3 rows,16th -3 rows,15th -3 rows,12th -2 rows, 11th -4 rows,9th -1 row,8th -1 row
我有 12 月 19 日 -1 行数据，第 18 行 -2 行，第 17 行 -3 行，第 16 行 -3 行，第 15 行 -3 行，第 12 行 -2 行，第 11 行 -4 行，第 9 行 -1 行，第 8 行 -1 行
You don't have data for all days. That is your problem and not the query. If you execute the query today - 22nd - you will only get data for 19th, 18th,17th,16th and 15th. You have no data for 20th, 21st and 22nd.
您没有整天的数据。那是您的问题，而不是查询。如果您今天执行查询 - 22 日 - 您将只会获得 19、18、17、16 和 15 日的数据。您没有 20 日、21 日和 22 日的数据。
EDIT - To get data for the last 7 days, where data is available you can try
编辑 - 要获取过去 7 天的数据，您可以尝试在数据可用的情况下
select id,
NewsHeadline as news_headline,
NewsText as news_text,
state,
CreatedDate as created_on
from News
WHERE CreatedDate IN (SELECT DISTINCT TOP 7 CreatedDate from News
order by createddate DESC)
回答by Mukesh Chauhan
DATEADD and GETDATE functions might not work in MySQL database. so if you are working with MySQL database, then the following command may help you.
DATEADD 和 GETDATE 函数在 MySQL 数据库中可能不起作用。因此，如果您正在使用 MySQL 数据库，那么以下命令可能对您有所帮助。
select id, NewsHeadline as news_headline,
NewsText as news_text,
state, CreatedDate as created_on
from News
WHERE CreatedDate>= DATE_ADD(CURDATE(), INTERVAL -3 DAY);
I hope it will help you
我希望它会帮助你
回答by Raji
Hope this will help,
希望这会有所帮助，
select id,
NewsHeadline as news_headline,
NewsText as news_text,
state,
CreatedDate as created_on
from News
WHERE CreatedDate >= cast(dateadd(day, -7, GETDATE()) as date)
and CreatedDate < cast(GETDATE()+1 as date) order by CreatedDate desc
回答by PKP
you can use DATEADDfunction in your where clause like
您可以在 where 子句中使用DATEADD函数，例如
select ...... where Createdate >= DATEADD(day,-7,GETDATE())
回答by Tanishq Joshi
This worked for me!!
这对我有用！！
SELECT * FROM `users` where `created_at` BETWEEN CURDATE()-7 AND CURDATE()
回答by Rishu
If you want to do it using Pentaho DI, you can use "Modified JavaScript" Step and write the below function:
如果你想使用 Pentaho DI 来做，你可以使用“ Modified JavaScript” Step 并编写以下函数：
dateAdd(d1, "d", -7); // d1 is the current date and "d" is the date identifier
Check the image below: [Assuming current date is : 22 December 2014]
检查下图：[假设当前日期是：2014 年 12 月 22 日]
Hope it helps :)
希望能帮助到你 ：）
