CODEWITHSUNDEEP
sql
user2211918
user2211918

Reputation: 91

Fetching each date data for a specified time imterval in sql

   SELECT * FROM #TempMatrixTable 
        WHERE  TIME >='07:00:00' and TIME <= '23:00:00' 
        ORDER BY dateperiod

In the above code am fetching data from 7 to 23 for each date its working fine.But when i try to fetch the data from 23 to 7 reverse of it am not able to get the result.Please provide solution to me.enter image description here

Please find the attached screenshot of the same above

  SELECT * FROM #TempMatrixTable 
        WHERE  TIME >='23:00:00' and TIME <= '07:00:00' 
        ORDER BY dateperiod

using the above query when i am trying to fetch the data.Am getting no rows because 23 is greater than 7 what could be the possible solution please help me 

  SELECT * FROM #TempMatrixTable 
        WHERE  TIME >='00:00:00' and TIME <= '24:00:00' 
        ORDER BY dateperiod

using the above query when i am ableto fetch the data.

Upvotes: 0

Views: 53

Answers (3)

DrCopyPaste
DrCopyPaste

Reputation: 4117

If you simply want to reverse the intervals selected you could just put NOT in front of the expression you want to have negated.

When

WHERE TIME >='07:00:00' and TIME <= '23:00:00'

gets you every entry in that interval (7hrs to 23 hrs)

WHERE NOT(TIME >='07:00:00' and TIME <= '23:00:00')

will get you the exact opposite, so every entry that is not inside that interval; but boundaries are now exclusive, because they were inclusive before and we negated, so if you want them included you should write:

WHERE NOT(TIME > '07:00:00' and TIME < '23:00:00')

Upvotes: 0

Guffa
Guffa

Reputation: 700212

When you are getting the times between 23 and 07, you are actually getting two time intervals; between 23 and 24, and between 00 and 07:

select
  *
from
  #TempMatrixTable 
where
  TIME >='00:00:00' and TIME < '07:00:00' or
  TIME >='23:00:00' and TIME <= '23:59:99.9999'
order by
  dateperiod

Naturally you don't need the 00 and 23.59 boundaries, as the time value can't be outside of those:

select
  *
from
  #TempMatrixTable 
where
  TIME < '07:00:00' or
  TIME >='23:00:00'
order by
  dateperiod

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want or instead of and:

SELECT *
FROM #TempMatrixTable 
WHERE TIME >='23:00:00' OR TIME <= '07:00:00' 
ORDER BY dateperiod;

Upvotes: 0

Related Questions