0
Order ID Picker ID Time_Picked
1 111 2020-01-13 11:28:00
2 222 2020-01-13 11:40:00
3 333 2020-01-13 10:00:00
4 444 2020-01-13 9:00:00
5 555 2020-01-13 9:45:00 
SELECT *
    FROM mytable
    WHERE Time_picked BETWEEN DATEADD(HOUR, -1, GETDATE()) AND CAST(GETDATE() AS DATE)

I am getting a blank output in my SQL Server 2016.

Considering time now is 12:00 PM. I want to return the first two rows (last hour rows).

Please help me with a function to find details of last 'x' minutes if there is any as well.

PS: I am regularly accessing this data from SQL Server and I am trying for it to be dynamic.

Any help would be appreciated. Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sai
  • 71
  • 7
  • a date without time is treated as if it were at time 00:00:00.000000, so if the current time is 09:23:45 you deduct 1 hour to 08:23:45 then ask for data that is between 08:23:45 and 00:00:00 it won't work as the first value must be less than the second value – Paul Maxwell Mar 31 '21 at 00:47

1 Answers1

2

Your time expression is:

WHERE Time_picked BETWEEN DATEADD(HOUR, -1, GETDATE()) AND CAST(GETDATE() AS DATE)

Under most circumstances, the first will be larger then the second, because the CAST() removes the time component. I suspect you want:

WHERE Time_picked BETWEEN DATEADD(HOUR, -1, GETDATE()) AND GETDATE()

Or assuming that all time values are in the past:

WHERE Time_picked >= DATEADD(HOUR, -1, GETDATE())
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I tried the first two expressions, they still give me a blank output. Can you please explain how the third expression works? – Sai Mar 31 '21 at 15:17