2

I'm creating a system for a school that needs to retrieve data from April of this year to June from last year.

I needed a query that can retrieve from this dates due to their school year always starts from June and end in April.

I tried selecting dates using Where clause.

"SELECT * FROM students WHERE year(c_date) = ? AND month(c_date) = ? AND c_occupation = 'Student'"

the question marks are use for java language which to prepare a statement using this code

pst=conn.prepareStatement(Sql);
            pst.setInt(1, year);
            pst.setInt(2, month);

I also tried this method but with this, I need to enter the date again after a year has pass. 

SELECT users.* FROM users 
WHERE dateadded <= '2019-06-01' 
AND dateadded >= '2018-4-30'

I wanted it to automatically based on what today's year is.

So for example today is March 20, 2019. I should be able to return all the values from this day until June 1, 2018

Dharman
  • 30,962
  • 25
  • 85
  • 135
Jin Anonuevo
  • 95
  • 8
  • Have you tried using the "between" operator? Also, you could try setting the date via PreparedStatement, instead of using int values: https://stackoverflow.com/questions/18614836/using-setdate-in-preparedstatement – sekky Apr 29 '19 at 18:42

2 Answers2

4

Your code can be :

LocalDate from = LocalDate.now().minusYears(1); // 2018-04-29
LocalDate to = LocalDate.of(Year.now().getValue(), Month.JUNE, 1); // 2019-06-01

String sql = "SELECT * FROM students WHERE dateadded BETWEEN ? AND ? AND c_occupation = 'Student'";
pst = conn.prepareStatement(Sql);
pst.setObject(1, from);
pst.setObject(2, to);
  • LocalDate.now().minusYears(1); return the same day of last year
  • LocalDate.of(Year.now().getValue(), Month.JUNE, 1); return the 1st June of the current year
  • dateadded BETWEEN ? AND ? will select all between the from and to dates
  • pst.setObject(1, from); if you are using JDBC 4+ you can set a LocalDate like so

Some references :

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
0

Your last method can be made to work using this approach

SELECT * FROM students 
WHERE dateadded BETWEEN (CONCAT(YEAR(NOW()), '-06-01') - INTERVAL 1 YEAR) AND CURDATE() 
AND c_occupation = 'Student';
Radagast
  • 5,102
  • 3
  • 12
  • 27