Select data between two times

Transact-SQL
1

Hi,

I want to select data from 6PM to 6AM for the last one week
my Date is in yyyy-mm-dd hh-mm-ss format

I tried this query:
SELECT *
FROM [myview]
where CONVERT(VARCHAR(8),myDate,108) between '18:00:00' and '6:00:00'
and myDate >= DATEADD(DAY,-7,GETDATE()) and myDate <=GETDATE()

This does not seem to work.
It is displaying data for the last seven days but only from 18:00:00 to 23:59:59
I do not see anything from 12:00:00am to 6:00:00am.

Not sure what i am doing wrong

2

where CONVERT(VARCHAR(8),myDate,108) not between '6:00:00' and '18:00:00'

3

You might try converting to TIME data type. Then SQL can use date functions to find the range.

4

Thanks for the reply.

I tried converting it to time.
CONVERT(TIME,myDate) between '18:00:00' and '6:00:00'
It doesn't return anything.

Thanks

5

where hour(myDate) not between 6 and 18

1 Like
6

Please note that between is needs the order to be small to large. So it would be CONVERT(TIME, myDate) NOT BETWEEN '6:00:00' and '18:00:00'

However, I like @bitsmed's solution.

[edit] add NOT - not that it matters :slight_smile: [/edit]

7

Thanks a lot guys :slight_smile:

This worked
DATEPART(HOUR, myDate) NOT BETWEEN 6 and 18