CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mansoor
Mansoor

Reputation: 33

filter the result of sql with time line

I need to select data based on date and time. I have two criteria. How can I implement this?

  1. Select the data between 1-1-2013 and 1-10-2013 with time rage between 10:00 to 16:00
  2. Select the data between 1-1-2013 and 1-10-2013 with time range between 20:00 to 08:00 the next morning

I implemented a code. It's only working for first criteria. Here is that code:

where date >= '1-1-2013' 
and date < '1-10-2013' 
and cast(date as time) between '10:00' and '16:00'

Here the date field in the table is datetime type. Please help to solve this.

Upvotes: 0

Views: 48

Answers (2)

Javlon Ismatov
Javlon Ismatov

Reputation: 194

  select * from your_table 
  where  date >='2013-01-01' and date <= '2013-01-11' 
  and convert(varchar,date,8)>'10:00' and convert(varchar,date,8)<'16:00'

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this using bunch of ORs:

select *
from your_table
where date >= '2013-01-01'
    and date < '2013-01-11'
    and (
        cast(date as time) between '10:00' and '16:00'
        or cast(date as time) >= '20:00'
        or cast(date as time) <= '08:00'
        );

Upvotes: 1

Related Questions