Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 selecting the nearest date time

Author  Topic 

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 09:21:58
Hi

I have a table with 2 columns, one is datetime which there is an entry for each hour of the day (2013-03-25 01:00:00.000) and the other are values (real). I want to select the value in the forthcoming hour so if I ran the query now (13:20 uk time) it would show me the value at 14:00

Can anyone help.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-26 : 09:28:03
This?
select valuecol 
from YourTable
where hourColumn = DATEADD(hh,datediff(mi,0,GETDATE())/60+1,0);
--where hourColumn = DATEADD(hh,datediff(hh,0,GETDATE())+1,0); -- or perhaps this?
Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 2013-03-26 : 09:31:32
spot on thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-26 : 11:18:41
You are very welcome - glad to help.

Check the boundary case where the current time is exactly on the hour whether it is giving you what you want. For example, this gives you 12:00 noon. Is that what you want?
select  DATEADD(hh,datediff(mi,0,'2013-03-26 11:00:00.000')/60+1,0);
Go to Top of Page
   

- Advertisement -