Can you add two DateAdd fields together? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can you add two DateAdd fields together?

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-27 : 10:21:07

I'd like the result of this to add the startTZ.Bias + the duration together. The error Im getting says that is not possible, is there a another way?

CONVERT(varchar, DATEADD(minute, startTZ.BIAS + DATEADD(minute,DURATION, SHIFTSTARTTIME), 108))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-27 : 10:23:14

maybe you can explain more what are you trying to achieve ?

Preferably with same sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-27 : 10:32:42

I'm trying to add the number of minutes contained in both StartTZ.Bias and Duration to the Shiftstarttime field

So if Shiftstartime is 09:00 and StartTZ.Bias is -300 the time would now be 4:00AM, then if the duration is 600 it would be 2:00PM. I'm just trying to get to the product of both time values.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-27 : 11:53:06

[code]DECLARE @Shiftstartime TIME = '09:00'

SELECT DATEADD(MINUTE, -300, @Shiftstartime)[/code]

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-27 : 12:20:18

I'm not too good with variables at this point, and the values I'm working with will all be different, not just 9:00 so Im not sure how I would set that up but would like to learn.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-27 : 12:34:09

Do you have a set of sample data that you can provide, so we can help you better? Here is a link that will show you how to perpare your DDL (tables), DML (insert statements) and expected output:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-09-27 : 12:46:41

I'm probably making it seem more complicated than what it is. Basically the start time in this table is stored in GMT time so I use a table that contains an offset to get it into the proper timezone. What I'm trying to do is Add the duration to the starttime - bias to get the end time.

So I think I just need away to do the math, here is an attempt I made:

MIN(CONVERT(varchar, DATEADD(minute, startTZ.BIAS + shiftassignment.duration,dbo.PLANNEDEVENTTIMELINE.STARTTIME), 108)) AS EndTime

The error said you can't add two DateAdd fields together, thats where I got stuck.

I'm trying to take the first start time of the day compensate for the time zone (bias) and then add the duration (in minutes) to arrive at the end-time.

Sorry if I made it more confusing.

First Last StartDate EndDate StartTime End Time Duration Bias
John Smith 9/29/2011 9/29/2011 1:45:00 630 -300
Mary Jane 9/29/2011 9/29/2011 9:00:00 510 -300

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-27 : 13:11:24

I don't get any errors with your code - although I don't know if the output is what you want:


;with yourTable (First, Last, StartDate, EndDate, StartTime, Duration, Bias)
as
(
select 'John', 'Smith', '9/29/2011', '9/29/2011', '1:45:00', 630, -300 union all
select 'Mary', 'Jane', '9/29/2011', '9/29/2011', '9:00:00', 510, -300
)

select 
       MIN(CONVERT(varchar, DATEADD(minute, BIAS+duration, STARTTIME), 108)) AS EndTime 
from   yourTable

OUTPUT:
EndTime
------------------------------
07:15:00

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-27 : 13:12:02

Did you read the link I posted? I'm still not sure what you want for output. But, here is a guess showing how to apply the forumla within a select statement:

DECLARE @Yak TABLE 
(
    [First] VARCHAR(50), 
    [Last] VARCHAR(50), 
    StartDate DATE, 
    EndDate DATE, 
    StartTime TIME, 
    --EndTime TIME, 
    Duration INT,
    Bias INT
)

INSERT @Yak
VALUES
('John', 'Smith', '9/29/2011', '9/29/2011', '01:45:00', 630, -300),
('Mary', 'Jane', '9/29/2011', '9/29/2011', '09:00:00', 510, -300)

SELECT 
    EndTime
FROM
    (    
        SELECT 
            DATEADD(MINUTE, Bias + Duration, StartTime) AS EndTime,
            ROW_NUMBER() OVER (PARTITION BY StartDate ORDER BY StartTime DESC) AS RowNum
        FROM @Yak
    ) AS T
WHERE
    RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources