| 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.
Advertisement
Answer
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())