CODEWITHSUNDEEP
sqlsql-servert-sqldatecompare
Santosh Jadi
Santosh Jadi

Reputation: 1527

Between date condition is not working in SQL Server

I have a data as below,

Data

Trying to run below query but returning 0 rows, Below query should return highlighted row data as shown above.

Can anybody please explain me, what i'm missing?

select * from Flt_OperativeFlight_SchedulePeriods
where  
(
    (cast('2018-04-05' as date) between cast(ScheduleStartDate as date) and   cast(ScheduleEndDate as date) )
    or
    (cast('2018-04-11' as date) between cast(ScheduleStartDate as date) and   cast(ScheduleEndDate as date) )
) 
and CarrierCode='SQ' and FlightNumber='0004'

Upvotes: 1

Views: 997

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could re-write as:

select * 
from Flt_OperativeFlight_SchedulePeriods 
where CarrierCode='SQ' and FlightNumber='0004' and
      (ScheduleStartDate >= '2018-04-05' and ScheduleEndDate <= '2018-04-11')

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

Seems like you want to get overlapping periods, then you need this logic:

start_1 <= end_2 and end_1 >= start_2

For your query:

where  
(
    cast('2018-04-05' as date) <= cast(ScheduleEndDate as date) 
    and
    cast('2018-04-11' as date) >= cast(ScheduleStartDate as date)
)

Depending on your logic you might have to < or >

Upvotes: 0

shiv sharma
shiv sharma

Reputation: 31

You can try this

SELECT * 
FROM `Flt_OperativeFlight_SchedulePeriods` 
WHERE ScheduleStartDate >= '2018-04-05' AND ScheduleEndDate <= '2018-04-11' 
      AND CarrierCode='SQ' and FlightNumber='0004'

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

It happens because

'2018-04-05' < '2018-04-06'
and
'2018-04-11' > '2018-04-10'

As variant maybe it is what you want

select * 
from Flt_OperativeFlight_SchedulePeriods 
where CarrierCode='SQ' and FlightNumber='0004' and
      (
           (ScheduleStartDate between '20180405' and '20180411')
        or (ScheduleEndDate between '20180405' and '20180411')
      )

Upvotes: 1

Related Questions