Sacre's answer is correct but outdated. The modern way is java.time classes.
TL; DR
StringBuild sql = new StringBuilder() sql.append( "SELECT id_, datum_ " ); sql.append( "FROM appointments_ " ); sql.append( "WHERE datum_ >= ? " ); // Start is *inclusive*… sql.append( "AND datum_ < ? ; " ); // …while end is *exclusive*. (Half-Open approach)
And pass a couple of Instant objects.
pstmt.setObject( 1 , start ) ; // Pass `java.time.Instant` object directly via `PreparedStatement::setObject` method. pstmt.setObject( 2 , stop ) ;
Use objects not strings
Avoid using strings to retrieve / store date and time values in your database. Use date and time objects for date and time values. Java and JDBC have rich support for date objects.
Using java.time
The java.sql.Date class is intended to represent values only for a date without time and without a time zone. In fact, he carries both of these additions, but does not pretend to be too much. Modern replacement for LocalDate . The LocalDate class represents a date value only without time and without a time zone.
But java.sql.Date (and LocalDate too) is the wrong class to use, since your column does not seem to be defined as a date type, but as a date type.
The critical issue here is ignored by your Question: time zone. The time zone is critical for determining the date . At any given moment, the date changes around the world by zone. For example, a few minutes after midnight in Paris France - this is a new day, still "yesterday" in Montreal Quebec .
Specify the time zone name in continent/region format, such as America/Montreal , Africa/Casablanca or Pacific/Auckland . Never use an abbreviation of 3-4 characters, such as EST or IST , as they are not real time zones, and are not standardized or even unique (!).
ZoneId z = ZoneId.of( "America/Montreal" ); LocalDate today = LocalDate.now( z );
As for the time zone in your database ...
- Your database will almost certainly store date and time values that display the time zone or offset information from UTC as UTC values in a type such as the SQL standard
TIMESTAMP WITH TIME ZONE . - If you use
TIMESTAMP WITHOUT TIME ZONE , then the values do not have information about the time zone or offset from UTC and do not represent actual moments on the timeline, but may be suitable for meetings far enough in the future that save them as zonal values carries the risk of colliding with policies that change the time zone definition, such as adjusting / accepting / canceling daylight saving time.
When saving as zoned values, you need to determine the start and end moments of your date in the desired time zone, then convert this pair of points to UTC values and query the database for this pair of UTC values.
Generally, it’s best practice to work with a time date to determine time intervals, such as the length of the day as needed here, using the Half-Open approach when the start of the range is on while the end is exclusive, So the day goes from the first moment of the day and goes up, but does not include, the first moment of the next day.
Do not assume that the first moment of the day is 00:00:00. Anomalies, such as Daylight Saving Time (DST), mean that the day can start at the same time, for example, 01:00:00. Let java.time determine the first moment of the day.
ZonedDateTime startZdt = localDate.atStartOfDay( z ); ZonedDateTime stopZdt = localDate.plusDays( 1 ).atStartOfDay( z );
Also remember that the time interval between this start and stop is not necessarily 24 hours. Anomalies, such as DST, mean that the day can last 23 or 25 hours or some other length.
Convert them to UTC using the extract Instant object. The Instant class represents a moment on the UTC timeline with a nanosecond resolution (up to nine (9) decimal digits).
Instant start = startZdt.toInstant(); // Convert to UTC value. Instant stop = stopZdt.toInstant();
To execute an SQL query, do not use BETWEEN . Instead of the Half-Open approach, this command uses the Closed method, where both start and end are included.
StringBuild sql = new StringBuilder() sql.append( "SELECT id_, datum_ " ); sql.append( "FROM appointments_ " ); sql.append( "WHERE datum_ >= ? " ); // Start is *inclusive*… sql.append( "AND datum_ < ? ; " ); // …while end is *exclusive*. (Half-Open approach) PreparedStatement pstmt = conn.prepareStatement( sql.toString() );
Specify a pair of Instant objects as arguments to be used by the SQL statement.
pstmt.setObject( 1 , start ) ; // Pass java.time.Instant object directly via `setObject` method. pstmt.setObject( 2 , stop ) ;
Sidebar: Add an underscore to all your SQL identifiers to avoid clashing names with a thousand reserved words with a promise of SQL specs.
Notice how we called setObject words directly with our java.time types in JDBC.
If your JDBC driver does not yet support JDBC 4.2 or later, you should revert to using obsolete java.sql types. In this case, we will need java.sql.Timestamp , but do it only briefly - try to stay in java.time as much as possible.
pstmt.setTimestamp( 1 , java.sql.Timestamp.from( start ) ) ; pstmt.setTimestamp( 2 , java.sql.Timestamp.from( stop ) ) ;
About java.time
The java.time framework is built into Java 8 and later. These classes supersede the nasty old legacy datetime classes such as java.util.Date , Calendar and SimpleDateFormat .
The Joda-Time project, now in maintenance mode , is advised to switch to the java.time classes.
To learn more, see the Oracle Tutorial . And search for qaru for many examples and explanations. JSR 310 specification .
Where to get java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is proof of possible future additions to java.time. Here you can find useful classes such as Interval , YearWeek , YearQuarter and more .