How to query between date and specific time range

Question

I need help on a query between date and specific time range. For example, a user entering the building from 1 Jan 2019 until 1 April 2019 and only from 7 PM until 10 PM for that date.

the database stored EVENTDATE as 01.01.2019, 07:15:01.000

I have tried per day and works

SELECT *
FROM event
WHERE eventdate BETWEEN '01.01.2019, 19:00:00' AND '01.01.2019, 22:00:00'

tried by date range and didn't works

SELECT *
FROM event
WHERE eventdate BETWEEN '01.01.2019' AND '01.02.2019'
  AND eventdate BETWEEN '19:00:00.000' AND '22:00:00.000'

Answer 1

I suspect you just need to use correct formats for the date/time values:

SELECT e.*
FROM event e
WHERE e.eventdate BETWEEN '2019-01-01 19:00:00' AND '2019-01-01 22:00:00'

Answer 2

You can convert the date to time first before adding a condition.

SELECT *
FROM event
WHERE eventdate BETWEEN '01.01.2019' AND '01.02.2019'
  AND time(eventdate) BETWEEN '19:00:00.000' AND '22:00:00.000'