MySQL SQL查询以查找上一个日期、当前日期和下一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34554002/
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
SQL query to find the previous date, current date and next date
提问by halfe
If the current date is 3/12/2015, then I need to get the files from dates 2/12/2015, 3/12/2015, 4/12/2015. Can anyone tell me an idea for how to do it?
如果当前日期是 3/12/2015，那么我需要从日期 2/12/2015、3/12/2015、4/12/2015 获取文件。谁能告诉我如何做的想法？
<%
try
{
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433/CubeHomeTrans","sa","softex");
Statement statement = con.createStatement() ;
ResultSet resultset = statement.executeQuery("
select file from tablename
where date >= DATEADD(day, -1, convert(date, GETDATE()))
and date <= DATEADD(day, +1, convert(date, GETDATE()))") ;
while(resultset.next())
{
String datee =resultset.getString("Date");
out.println(datee);
}
}
catch(SQLException ex){
System.out.println("exception--"+ex);
}
%>
This is the query I have done, but it's erroneous. I need to get the previous date, current date and next date.
这是我所做的查询，但它是错误的。我需要获取上一个日期、当前日期和下一个日期。
回答by Saharsh Shah
Use DATE_ADD()And DATE_SUB()functions:
使用DATE_ADD()和DATE_SUB()函数：
Try this:
尝试这个：
SELECT FILE, DATE
FROM ForgeRock
WHERE STR_TO_DATE(DATE, '%d/%m/%Y') >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
AND STR_TO_DATE(DATE, '%d/%m/%Y') <= DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY);
Check the SQL FIDDLE DEMO
::OUTPUT::
：：输出：：
| file | DATE |
|------|------------|
| dda | 31/12/2015 |
| ass | 01/01/2016 |
| sde | 02/01/2016 |
回答by Sahi
You can use dateAdd function
您可以使用 dateAdd 功能
syntax
句法
DATEADD(datepart,number,date)
i.e for current date
即当前日期
select GETDATE()
for yesterday
昨天
select DATEADD(D,-1,GETDATE())
for tomorrow
为了明天
select DATEADD(D,1,GETDATE())
so, your query should be like
所以，你的查询应该像
select file from tablename
where date >= DATEADD(D,-1,GETDATE())
and date <= DATEADD(D,1,GETDATE())
回答by hud
Simplest way to get all these datesare as below:-
获取所有这些日期的最简单方法如下：-
CURRENT DATE
当前的日期
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
NEXT DAY DATE(Adding 1 to the dateaddparameter for one day ahead)
NEXT DAY DATE （dateadd提前一天的参数加1）
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
YESTERDAY DATE(Removing 1 from the datediffparameter for one day back)
YESTERDAY DATE （前datediff一天从参数中删除 1 ）
SELECT DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
If you go through the link here, you will get an amazing way of explanation for getting date. It will clear your logic and will be useful for future reference too.
如果你通过这里的链接，你会得到一个惊人的解释方式date。它将清除您的逻辑，也将有助于将来参考。
Hope that helps you
希望能帮到你
回答by Vishnu Babu
current date
当前的日期
date = (SELECT CONVERT(char(10), GetDate(),126))
yesterday
昨天
date = (SELECT dateadd(day,datediff(day,1,GETDATE()),0))
next day
明天
date= SELECT DATEADD(day, 1,(convert(date, GETDATE())))
