How to add hours to a timestamp?

Posted on 2006-05-18
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
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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

    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

    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

    Thats it, thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now