How to add hours to a timestamp?

Posted on 2006-05-18
Medium Priority
Last Modified: 2012-05-05
I have a situation where I need to add hours to a timestamp. The only thing is that the hours could have .25 increments, with .25 representing 15 minutes.

My starting time will always be the same time such as 08:00 AM
If I needd to add the number 3.75 to this so that I end up with a time of 11:45AM how would I do this in my sql statement?

activity_dttm  = '05/18/2006 8:00:00 AM'

IF I do
SELECT DATEADD(hour, 1.25, cast( sum(cast(activity_dttm as float) )as datetime))
from my_timetable
then I ge the correct hour 11:00 AM but not the minutes of course. I know one way would be to parse my hour for either .00,.25,.50 or .75 since it can ONLY be one of those then set a variable = to either 0,15,30 or 45 to represent quarter of hours then do another Dateadd and add those minutes. I'm just wondering if there is a way to do it in a sql statement.

Question by:byteboy1
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16713749
you will indeed have to parse.

SELECT DATEADD( minute ,  100.0 * 100.0 * (1.25 - cast(1.25 as int)) / 60.0 ,    DATEADD(hour, cast(1.25 as int), cast( sum(cast(activity_dttm as float) )as datetime)))
from my_timetable
LVL 11

Expert Comment

ID: 16715152
Unless I misunderstood, no parsing - you can use DATEADD of minutes, or seconds if you need more accuracy, but I think the following will work fine for you:

SET @HOURS = 3.75
select DATEADD(minute, @Hours * 60, GETDATE())

LVL 11

Accepted Solution

anyoneis earned 1000 total points
ID: 16715163
Or to use your numbers:

DECLARE @activity_dttm DATETIME
SET @activity_dttm  = '05/18/2006 8:00:00 AM'

SELECT DATEADD(minute, 1.25 * 60, @activity_dttm)


Author Comment

ID: 16719334
Thats it, thanks!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question