0

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'
munsifali
  • 1,732
  • 2
  • 24
  • 43
Shazwan Shukor
  • 1
  • 1

2 Answers2

0

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'
munsifali
  • 1,732
  • 2
  • 24
  • 43
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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'
munsifali
  • 1,732
  • 2
  • 24
  • 43
Nap
  • 8,096
  • 13
  • 74
  • 117