SQL query to find the previous date, current date and next date

Question

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?

<%
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.

Answer 1

Use DATE_ADD() And DATE_SUB() functions:

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 |

Answer 2

Simplest way to get all these dates are as below:-

CURRENT DATE

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

NEXT DAY DATE (Adding 1 to the dateadd parameter for one day ahead)

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

YESTERDAY DATE (Removing 1 from the datediff parameter for one day back)

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.

Hope that helps you