CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Kcird
Kcird

Reputation: 97

SQL Server 2012 query returns different date

I want to select a specific time schedule of a certain date which is 10-04-2018 but it returns a value of 10-03-2018.

SELECT *
FROM tbl_schedule
WHERE [Date] = '10-04-2018'
    AND [StartTime] BETWEEN '8:01'
        AND '9:59'
    OR [EndTime] BETWEEN '8:01'
        AND '9:59';

enter image description here

Upvotes: 1

Views: 78

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

I'm pretty sure it's because of your OR clause.

OR [EndTime] BETWEEN '8:01'
        AND '9:59';

It might be easier to understand how your query works if you add some parenthesis:

SELECT *
FROM tbl_schedule
WHERE ( [Date] = '10-04-2018'
        AND [StartTime] BETWEEN '8:01'
        AND '9:59' )
    OR 
    ( [EndTime] BETWEEN '8:01'
        AND '9:59' ) ;

This happens by default because of how operator precedence is set up. You can find more information about it, here.

But, in short, AND has a higher operator precedence than OR, which is why it will be evaluated first, putting together your first two conditions [Date] = & [StartTime] BETWEEN, then the OR will be applied on this result combined with the [EndTime] BETWEEN condition.

I recommend using parenthesis when using OR and I think the query you're looking for is:

SELECT *
FROM tbl_schedule
WHERE [Date] = '10-04-2018'
      AND 
      (  [StartTime] BETWEEN '8:01' AND '9:59' 
      OR [EndTime] BETWEEN '8:01' AND '9:59' ) ;

Upvotes: 6

Mohamed Hasan
Mohamed Hasan

Reputation: 237

You should use "(" after AND in case you need the date on this day only:

    SELECT *
FROM tbl_schedule
WHERE [Date] = '10-04-2018'
    AND ( [StartTime] BETWEEN '8:01'
        AND '9:59'
    OR [EndTime] BETWEEN '8:01'
        AND '9:59')

Upvotes: 3

Related Questions