DateAdd() - how to add 5 hours and 25 minutes to a date?

  • Jens Gerbitz

    Old Hand

    Points: 338

    Hi there,

    This is actually a question re how to do this in SSRS's Report Designer 1.0, please forgive me if this isn't the right site, but I'm close to losing the plot on this.

    Basically, I have a date to which I need to add a time span consisting of multiple (what MS calls) dateparts. All of the sites / forums I've come across deal only with the syntax of one datepart, i.e. adding days OR hours OR minutes, etc.

    I need to add hours AND minutes using DateAdd()... Appreciate any help!

    Many thanks,

    Jens

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    try something like this:

    DATEADD(HOUR,5,DATEADD(MINUTE,25,"origdatetime"))

  • Jens Gerbitz

    Old Hand

    Points: 338

    You did it, Batgirl! Thanks, I seriously owe you a beer, my weekend is now officially made.

    Cheers,

    Jens

  • batgirl

    SSCarpal Tunnel

    Points: 4979

    Glad it worked - I will enjoy that beer!

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    This also works:

    select

    Curr_Datetime= getdate(),

    [Curr_Datetime Plus 05:25] =getdate()+'05:25:00.000'

    Results:

    Curr_Datetime Curr_Datetime Plus 05:25

    ----------------------- ------------------------

    2013-08-09 14:52:58.457 2013-08-09 20:17:58.457

  • asksubir

    SSC Enthusiast

    Points: 181

    --https://www.linkedin.com/in/subirsdas/--
    DECLARE @span1 time--
    SET @span1='2:09:54.0566667'
    DECLARE @span_hr INT,@span_mi INT,@span_ss INT --Breaking into hr, min and second
    SET @span_hr = datepart(hh,@span1)
    SET @span_mi = datepart(mi,@span1)
    SET @span_ss = datepart(ss,@span1)

    SELECT @span1 AS RequiredToAddTimePart --14:09:54.0566667 printing required timepart to be add

    DECLARE @dt datetime2 = null
    SET @dt =GETDATE() --Current date or any Date, which you want to add
    SELECT @dt AS CurrentDatetime

    DECLARE @OLDTime TIME
    SELECT @OLDTime=CONVERT(time,convert(VARCHAR(2),@span_hr)+':'+CONVERT(VARCHAR(2),@span_mi)+':'+CONVERT(VARCHAR(10),@span_ss))
    SELECT DATEADD(HOUR,@span_hr,DATEADD(MINUTE,@span_mi,DATEADD(SECOND,@span_ss,@dt))) AS AddedNewTimePart1

  • nigel.

    SSChampion

    Points: 11481

    --
    Nigel

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply