CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Rojish Varughese
Rojish Varughese

Reputation: 93

Time based filtering using sql

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

1)select the data between 1-1-2013 and 1-10-2013 with time rage between 10 to 16

2)select the data between 1-1-2013 and 1-10-2013 with time range between 20 to 8 next morning

I implemented a code.Its 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.Pleases help to solve this

Upvotes: 4

Views: 26042

Answers (3)

Shapka
Shapka

Reputation: 21

did i understand correctly? i don't know..

try this

where date>='1-1-2013' AND date <'1-10-2013' 
     AND ((CAST(date AS TIME) between '10:00' and '16:00')
      OR (CAST(date AS TIME) between '20:00' and '23:59')
      OR (CAST(date AS TIME) between '00:00' and '08:00'))

Upvotes: 1

sangram parmar
sangram parmar

Reputation: 8726

Try this:

DECLARE @tmp TABLE ( date DATETIME )

INSERT  INTO @tmp
        ( date )
VALUES  ( '2013-01-01 10:09:29'  -- date - datetime
          )
INSERT  INTO @tmp
        ( date )
VALUES  ( '2013-01-01 15:09:29'  -- date - datetime
          )
INSERT  INTO @tmp
        ( date )
VALUES  ( '2013-01-01 17:09:29'  -- date - datetime
          )
INSERT  INTO @tmp
        ( date )
VALUES  ( '2013-01-01 07:09:29'  -- date - datetime
          )
SELECT  date
FROM    @tmp AS t
WHERE   CONVERT(DATE,date) >= CONVERT(DATE, '01-01-2013', 105)
        AND CONVERT(DATE,date) <= CONVERT(DATE, '01-01-2013', 105)
        AND CONVERT(TIME, date) BETWEEN CONVERT(TIME, '10:00')
                                AND     CONVERT(TIME, '16:00')

Upvotes: 1

Greg
Greg

Reputation: 3522

1) 

WHERE Date Between '2013-01-01 10:00' AND '2013-10-01 16:00'

2)

WHERE Date Between '2013-01-01 20:00' AND '2013-10-01 08:00'

Upvotes: 1

Related Questions