Get data between the dates

Question

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

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

Here Date1 and Date2 is the Jdatechooser, taken from user.

 Error:   
 You have an error in your SQL syntax; check the manual that
 corresponds to your MariaDB server version for the right syntax to use
 near 'Date >= Jun 12, 2017 Date <= Jun 14, 2017' at line 1

Answer 1

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.

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

And…

Instant instant = myResultSet.getObject( … , Instant.class ) ;
ZonedDateTime zdt = 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.

Answer 2

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+") ";

Answer 3

Try to qoute the dates 'date1'.

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

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

Answer 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);