The Answer by Sacre is correct but outdated. The modern way is with the 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 pair 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 for fetching/storing date-time values in your database. Use date-time objects for date-time values. Java and JDBC have rich support for date-time objects.
Using java.time
The java.sql.Date class is intended to represent a date-only value without a time-of-day and without a time zone. Actually it carries both those extras, but pretends not too. The modern replacement is LocalDate. The LocalDate class represents a date-only value without time-of-day and without time zone.
But java.sql.Date (and LocalDate too) is the wrong class to use as your column seems to be defined not as a date-only type but as a date-time type.
The critical issue here is being ignored by your Question: time zone. A time zone is crucial in determining a date. For any given moment, the date varies around the globe by zone. For example, a few minutes after midnight in Paris France is a new day while still “yesterday” in Montréal Québec.
Specify a proper time zone name in the format of continent/region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 3-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).
ZoneId z = ZoneId.of( "America/Montreal" );
LocalDate today = LocalDate.now( z );
Regarding time zone in your database…
- Your database almost certainly stores date-time values carrying time zone or offset-from-UTC info as UTC values in a type such as the SQL standard
TIMESTAMP WITH TIME ZONE.
- If using
TIMESTAMP WITHOUT TIME ZONE, then the values have no time zone or offset-from-UTC info, and do not represent actual moments on the timeline, but may be appropriate for appointments far enough into the future that storing them as zoned values carries the risk of running afoul of politicians changing the time zone definition such as adjusting/adopting/dropping Daylight Saving Time.
If stored as zoned values, you need to determine the starting and ending moments of your date in your desired time zone, then convert that pair of points to UTC values, and query the database for that pair of UTC values.
Generally the best practice in date-time work for defining spans of time, such as the duration of the day as needed here, is to use the Half-Open approach where the beginning of the span is inclusive while the ending is exclusive. So a day runs from first moment of the day and goes up to, but does not include, the first moment of the following day.
Do not assume the first moment of the day is 00:00:00. Anomalies such of Daylight Saving Time (DST) means the day may start at a time such as 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 span of time between that start and stop is not necessarily 24-hours. Anomalies such as DST mean the day may run 23 or 25 hours, or some other length.
Convert those to UTC by extract Instant object. The Instant class represents a moment on the timeline in UTC with a resolution of nanoseconds (up to nine (9) digits of a decimal fraction).
Instant start = startZdt.toInstant(); // Convert to UTC value.
Instant stop = stopZdt.toInstant();
To do the SQL query, do not use BETWEEN. Instead of the Half-Open approach, that command uses the Closed approach where both beginning and ending is inclusive.
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 your pair of Instant objects as the 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: Append a trailing underscore to all your SQL identifiers to avoid any name collision with the one thousand reserved words, per a promise by the SQL specification.
Note how we called setObject to word directly with our java.time types in JDBC.
If your JDBC driver does not yet support JDBC 4.2 or later, you must fall back to using the legacy java.sql types. In this case we would need java.sql.Timestamp. But do so 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 supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
