How to compare datetime range between 4 datetime columns in SQL Server

Question

I have 4 datetime columns in my table way_point. The four columns are

scheduled_a, checkin_time, no_later_than, no_earlier_than

Now I want to see if values in scheduled_a, checkin_time fall between the range of no_later_than and no_earlier_than columns.

This is the code that I have written but I get this error:

An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

Code:

select 
    customer_id, position 
from 
    way_points 
where 
    position = 2 
    and [scheduled_a] and [checkin_time] between [no_later_than] and [no_earlier_Than]

Position is here another column which I must use as a condition in where. But mostly I am stuck at comparing the datetime columns.

Any idea where I might be wrong please? Thank you.

Answer 1

You were almost there. You need to split it into two different conditions. Can't say whether no_later_than or no_earlier_than is bigger. After between the first argument needs to be the smaller one. For example if no_later_than = '2016-02-22' and no_earlier_than = '2016-02-23' then no_later_than is the smaller argument.

select customer_id,position 
from way_points where position=2 
and [scheduled_a] between no_later_than and [no_earlier_than]
and [checkin_time] between no_later_than and [no_earlier_than]