SQL adding DateTimeOffset and Time (time span) together

I have two fields, DateTimeOffset (the game start date and time) and Time(the game duration).  Time in the application is 0:30 hr:mm to 2:45 hr:mm, resolution is 1 minute.
I need to get the Date, StartTime and EndTime . I hve not been able to get DATEADD to work for me to add the two fields together.

SELECT ID
			, CONVERT(date, GameTimeStart) --Returns the date OK
			, CONVERT(time, GameTimeStart) --Returns the start time OK
			, CONVERT(time, DATEADD(????, GameTimeStart, GameDuration)) --ERROR should return the game end time
	FROM Games
	WHERE ScheduleID = @ScheduleID

Open in new window



Kind Regards
Sam
SamCashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Please post some of the raw data.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you want to use the DateDiff function:

SELECT ID
			, CONVERT(date, GameTimeStart) --Returns the date OK
			, CONVERT(time, GameTimeStart) --Returns the start time OK
			, CONVERT(time, DateDiff( mi, GameTimeStart, GameDuration)) --ERROR should return the game end time
	FROM Games
	WHERE ScheduleID = @ScheduleID

Open in new window


this'll give you the difference in minutes. if you need something other than minutes, consult the table of constants you can use in the link above
0
SamCashAuthor Commented:
Thank you,
dsacker, here is some data, bigmonty, I need the sum.

DECLARE (@GameTimeStart datetimeoffset(0) = '2015-06-15 12:30'
DECLARE (@GameDuration time(0) = '01:30:00'


INSERT INTO two columns in a table GameTimeStart and GameDuration

I know this does not work but I hope it better explains the objective.
SELECT GameTimeStart + GameDuration AS GameTimeEnd --  -- Result should be '2015-06-15 14:00'

to furthur express the objective in C it would be

            DateTimeOffset GameTimeStart = new DateTimeOffset(2015,06, 15, 12,30,00, new TimeSpan(2,0,0));
            TimeSpan GameDuration = new TimeSpan(1, 30, 00);
            DateTimeOffset GameTimeEnd;
            
            GameTimeEnd= GameTimeStart + GameDuration;

Open in new window


Thanks again
Sam
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

dsackerContract ERP Admin/ConsultantCommented:
You're coding this in ASP, right?

But you're wanting the equivalent in SQL, right?

You'll want to code it as follows:

DECLARE @GameTimeStart  datetime,
        @GameDuration   time

SET @GameTimeStart = '2015-06-15 12:30'
SET @GameDuration  = '01:30:00'

SELECT  @GameTimeStart                  AS GameTimeStart,
        @GameDuration                   AS GameDuration,
        @GameTimeStart + @GameDuration  AS GameTimeEnd

Open in new window

The trick is to make sure the @GameTImeStart is a datetime data type (in SQL Server).
0
SamCashAuthor Commented:
dsacker,

My table data types are already fixed, (they seem right for the application).
I got it working...  Now I need to do this in "BestPractices".  Suggestions please as this seems to complicated for the simple function of adding a time type to a datetimeoffset type and returning a datetimeoffset type, hmmm?

This line works.
SELECT ToDateTimeOffset(CONVERT(datetimeoffset, GameTimeStart) + CONVERT(datetime, GameDuration),DATEPART(TZOFFSET,GameTimeStart)) as CorrectResult

Open in new window


Building up to the working solution.
SELECT ID
			, CONVERT(datetimeoffset, GameTimeStart) as StartDateTimeOffset --to see the data
			, CONVERT(date, GameTimeStart) as StartDate
			, CONVERT(time, GameTimeStart) as StartTime
			, CONVERT(time, GameDuration) as Duration
			
			, CONVERT(datetimeoffset, GameTimeStart) + CONVERT(datetime, GameDuration) as GameTimeEnd --Adding works, but losing the Offset
			
			, DATEPART(TZOFFSET,GameTimeStart) as TZ --to see the offset data
			
			, ToDateTimeOffset(CONVERT(datetimeoffset, GameTimeStart) + CONVERT(datetime, GameDuration),DATEPART(TZOFFSET,GameTimeStart)) as CorrectResult
			
	FROM Games
	WHERE ScheduleID = @ScheduleID

Open in new window


Thanks for contributing to this solution and for your recommendation for doing this right.

Kind Regards
Sam
0
SamCashAuthor Commented:
dsacker,

See above, do you have a recommendation for "Best Practices"?  This seems overly complicated.

Regards
Sam
0
dsackerContract ERP Admin/ConsultantCommented:
Actually, although it's a but cumbersome to read, it is quite efficient. Good job.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
dsacker,

Thanks much for all your help on this.  I hope it assists others.

Please look at my other issue # 40862921 .

Kind Regards
Sam
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.