Link to home
Start Free TrialLog in
Avatar of SamCash
SamCashFlag for United States of America

asked on

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
Avatar of dsacker
dsacker
Flag of United States of America image

Please post some of the raw data.
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
Avatar of SamCash

ASKER

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
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).
Avatar of SamCash

ASKER

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
Avatar of SamCash

ASKER

dsacker,

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

Regards
Sam
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SamCash

ASKER

dsacker,

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

Please look at my other issue # 40862921 .

Kind Regards
Sam