Get Data Between The Dates

String Date1 = (((JTextField)jDateChooser1.getDateEditor().getUiComponent()).getText()); String Date2 = (((JTextField)jDateChooser2.getDateEditor().getUiComponent()).getText()); S

Solution 1:

Use STR_TO_DATE function;

String query="SELECT * FROM work_hours WHERE ID ="+A+" AND Date >= STR_TO_DATE("+Date1+") AND Date <= STR_TO_DATE("+Date2+") ";

Solution 2:

Date-time types

For date-time values, use date-time data types to define your column, and use date-time classes in Java. The job of your JDBC driver is to mediate between these types.

You are trying to pass strings rather than date-time objects.

Half-Open logic

In date-time work, use Half-Open approach where the beginning is inclusive while the ending is exclusive. So lunch starts at noon and runs up to, but does not include, the first moment of 1 PM. A week starts at Monday and runs up to, but does include, the following Monday.

SELECT*FROM tbl_
WHERE when_ >= ?   -- Pass start moment. Inclusive.AND   when_  < ?   -- Pass stop moment.  Exclusive.
;

The SQL command BETWEEN is “closed” meaning both the beginning and ending are inclusive; not good for date-time work.

Parse strings to date-time

You need to transform your user-input into date-time objects. You may want to parse a string types by user. Or you may want to use a date-time widget. In your case, parsing strings is apparently needed. Search Stack Overflow for DateTimeFormatter to find hundreds of existing Questions and Answers.

SQL & JDBC

The Instant class in Java represents a moment on the timeline in UTC. Equivalent to the legacy java.util.Date class but with a finer resolution of nanoseconds rather than milliseconds.

Apply a time zone ZoneId to get a ZonedDateTime object. Equivalent to the legacy class GregorianCalendar.

ZonedDateTimezdt= ZonedDateTime.parse( input , … ) ;
myPreparedStatement.setObject( … , zdt.toInstant() ) ;

And…

Instantinstant= myResultSet.getObject( … , Instant.class ) ;
ZonedDateTimezdt= instant.atZone( ZoneId.of( "America/Montreal" ) ) ;

Tips

Observe naming conventions. In Java, variables start with a lowercase letter.

Avoid naming columns in database with reserved words. Easiest way to entirely avoid all reserved words is to append a trailing underscore to all the names of all your database objects. The SQL standard explicitly promises to never use a trailing underscore.

Solution 3:

Try to qoute the dates 'date1'.

String query="SELECT * FROM work_hours WHERE ID ="+A+" 
AND Date >= '"+Date1+"' AND Date <= '"+Date2+"' "; 

Solution 4:

You can try to use 'BETWEEN'.

Exempel:

SELECT * FROM table_name WHERE date BETWEEN '2017-07-01 07:07:07' AND '2017-07-31 07:07:07';

I hope I was able to help you.

Solution 5:

To solve this issue you need to accomplish to steps:

• Proper date format from JAVA snippet
• Add single quotes wrapping dates in SQL script

I'm not sure how you format a date variable en JAVA to return the ANSI date standard 'YYYYMMDD'

The SQL script needs to look like this:

String query="SELECT * FROM work_hours WHERE ID ="+A+" AND Date >= '"+Date1+"' AND Date <= '"+Date2+"' ";
ResultSet rs = db.Select(query);