CODEWITHSUNDEEP
sql-server
beliskna
beliskna

Reputation: 185

Fetch next date from based on a date parameter

I have a requirement to fetch back the next date based on a date I already know. I am using the following query but this returns all dates and all nextdates for the entire recordset. I can understand that I am asking for dates greater than my search date, but if I use WHERE = '2010-01-01 04:30:00.000' then I get no results. How can I fetch only the next date?

The query is (using SQL Server 2012 Express):

SELECT [DateTime], [Item], LEAD([DateTime]) OVER (ORDER BY [DateTime]) NextDate
FROM cteList
WHERE [DateTime] > '2010-01-01 04:30:00.000'
ORDER BY [DateTime];

Upvotes: 0

Views: 110

Answers (1)

SQLChao
SQLChao

Reputation: 7837

As others have stated you can limit your existing query TOP 1

SELECT TOP 1 [DateTime], [Item], LEAD([DateTime]) OVER (ORDER BY [DateTime]) NextDate
FROM cteList
WHERE [DateTime] > '2010-01-01 04:30:00.000'
ORDER BY [DateTime];

For you to use WHERE [DateTime] = you would have to have the exact datetime to the corresponding row in your table.

Upvotes: 1

Related Questions