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.

Solution 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 |

Solution 2

You can use dateAdd function

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())

Solution 3

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

Comments

[Nad]

your last query for next day wont run in SQL server 2005 because Date doesn't work in 2005 you can use datetime in place of date for that. Rest all is working fine. Also, you need to mention what is date = (.....

[halfe]

is these query help to find the files of yesterday and tomorrow?? i need to get the files of current date too

[halfe]

is these query help to find the files of yesterday and tomorrow?? i need to get the files of current date too

[Saharsh Shah]

@halfe The above query will give you the data of 1 day prior to DATE column, 'Date` column and 1 day after of DATE column. If Date column id today's date then above query will return today, yesterday and tomorrow's data

[halfe]

can u please tell me how to add that in SELECT FILE FROM tablename WHERE DATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND DATE <= DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY);

[Saharsh Shah]

@halfe What do you want to do?

[halfe]

the query should return the files on current date,previous day of current date and next day of current date

[Saharsh Shah]

@halfe Please add your table structure so that I can explain you

[halfe]

CREATE TABLE ForgeRock (id int,'file' string, date varchar(20)) ; INSERT INTO ForgeRock (id,'file', date) VALUES (0,dda,'31/12/2015'), (1, ass,'01/01/2016' ), (2, sde,'02/01/2016' ), (3,gff ,'03/01/2016'), (4, sfd,'04/01/2016'), (5, abc ,'05/01/2016') ;

[Sahi]

yes. where condition is checking yesterday's to tomorrow's date. i.e yesterday, today and tomorrow .

[halfe]

i am using ms sql when i run the query i got an exception exception--com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'CURRENT_DATE'. can it be solved?

[Saharsh Shah]

@halfe For SQL Server the functions are different so you have to change those functions in your query