For example, I have a medication protocol that starts May 1, 2011 at 1PM and this medication is given 3 times within 24 hours then the dose changes, I want my next protocol to start on May 2, 2011 at 1:01 PM, I need the 24 hour window to reflect true time not restart at midnight. Any suggestions?
I think you are looking for the dateadd function. To add 24 hours to a DateTime value you simply add 1 day or 24 hours to the original value. DECLARE @date datetime SET @date = '2011/05/01 1PM' SELECT @date, DATEADD(day, 1, @date) as NexDate, DATEADD(hour, 24, @date) AS NextDate2 if you need to add another minute you can use DECLARE @date datetime SET @date = '2011/05/01 1PM' SELECT @date, DATEADD(minute, 1, DATEADD(day, 1, @date)) as NexDate, DATEADD(minute, 1, DATEADD(hour, 24, @date)) AS NextDate2 For details on the DATEADD function take a look on [MSDN DATEADD (Transact-SQL)] :
You should be able to use datediff by hours if you are wanting to compare two times. I am not absolutely sure what you are looking for. Can you give an example of how you are wanting this to look in a table or application?