SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 selecting the nearest date time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

silentbob
Starting Member

18 Posts

Posted - 03/26/2013 :  09:21:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 03/26/2013 :  09:28:03  Show Profile  Reply with Quote
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 - 03/26/2013 :  09:31:32  Show Profile  Reply with Quote
spot on thanks
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 03/26/2013 :  11:18:41  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000