HSQLDB Query given two dates and time

Question

I need to get the list of users from the date and time entered to the date and time left(but also give the results if no date and time entered). Something is wrong with the results of this query, especially when i type the date/time in/left. The search form works for the rest of the fields (like Name, Vehicle Number, Level, Unit).

SELECT * FROM "tblVehicles"
WHERE
((UPPER("Name") LIKE '%' ||

UPPER(:VAR_NAME) || '%')

OR

(:VAR_NAME is NULL))

AND

((UPPER("Vehicle Number") LIKE '%' ||

UPPER(:VAR_VEHICLE) || '%')

OR

(:VAR_VEHICLE is NULL))

AND

((UPPER("Level") LIKE '%' ||

UPPER(:VAR_LEVEL) || '%')

OR

(:VAR_LEVEL is NULL))

AND

((UPPER("Unit") LIKE '%' ||

UPPER(:VAR_UNIT) || '%')

OR

(:VAR_UNIT is NULL))

AND

((("Date In" >= :VAR_DATEIN) AND ("Time In" >= :VAR_TIMEIN))

OR

((:VAR_DATEIN is NULL)

OR

(:VAR_TIMEIN is NULL)))

AND

((("Date Left" <= :VAR_DATELEFT) AND ("Time Left" <= :VAR_TIMELEFT))



OR

((:VAR_DATELEFT is NULL)

OR

(:VAR_TIMELEFT is NULL)))

Answer 1

Most likely, the dates have not been entered in the correct format in the parameter input dialog. As an example, create a new database with tblVehicles using the following schema.

In the data entry dialog, right-click on date columns and format as YYYY-MM-DD, and time columns HH:MM. These formats are the least ambiguous. Then enter the following data.

Now create the following query in SQL view.

SELECT * FROM "tblVehicles"
WHERE ((("Date In" >= :VAR_DATEIN) AND ("Time In" >= :VAR_TIMEIN))
    OR ((:VAR_DATEIN is NULL)
    OR (:VAR_TIMEIN is NULL)))
AND ((("Date Left" <= :VAR_DATELEFT) AND ("Time Left" <= :VAR_TIMELEFT))
    OR ((:VAR_DATELEFT is NULL)
    OR (:VAR_TIMELEFT is NULL)))

Then run the query. Enter 2017-02-01 for VAR_DATEIN which autocorrects to #02/01/2017. Enter 8:00 for VAR_TIMEIN which autocorrects to #08:00:00#.

The result:

There is a FAQ for Base that describes date ranges: https://wiki.documentfoundation.org/Faq/Base/122.