Datetime fetch incorrect data for second iteration

I have this query :

Declare @t table ( StartDate datetime , ENDDate datetime)

Insert into @t values ('2020-02-05 15:00:00.000','2020-02-05 15:30:00.000')
Insert into @t values ('2020-02-05 15:10:00.000','2020-02-05 15:20:00.000')

Declare @time datetime ='2020-02-05 15:11:00.000'

select top 1 * from @t
where StartDate <= @time 
order by StartDate desc

It gives me 2nd row as an output which is correct. Because time is 15:11:00.000.

Now If I pass

Declare @time datetime ='2020-02-05 15:31:00.000'

And I run the above query it still gives me the same output, But I want to show the 1st row as an output. What changes do I need to make to satisfy both the requirement with 1 query?

The reason I want to show the 1st row as an output: Because the enddate which is being passed is near to the end date of the 1st row, I can write the second query to fetch the data, But I need just 1 query wherin both the condition should be satisfied.

I tried this as well:

Declare @t table ( StartDate datetime , ENDDate datetime)

Insert into @t values ('2020-02-05 15:00:00.000','2020-02-05 15:30:00.000')
Insert into @t values ('2020-02-05 15:10:00.000','2020-02-05 15:20:00.000')

Declare @time datetime ='2020-02-05 15:11:00.000'

select *,DATEDIFF(MINUTE,ENDDate,@time) DTB from (
select top 1 * from @t
where StartDate <= @time  
order by StartDate desc 
) a
order by dtb

But I am still getting the same output.

Answer

Can you please try this below logic-

DEMO HERE

Declare @t table ( StartDate datetime , ENDDate datetime)

Insert into @t values ('2020-02-05 15:00:00.000','2020-02-05 15:30:00.000')
Insert into @t values ('2020-02-05 15:10:00.000','2020-02-05 15:20:00.000')

--Declare @time datetime ='2020-02-05 15:11:00.000'
Declare @time datetime ='2020-02-05 15:31:00.000'

select TOP 1 *
from @t
where StartDate <= @time 
ORDER BY ABS(datediff(mi,ENDDate,@time))

Advertisement

Answer