SamCash
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.
Kind Regards
Sam
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
Kind Regards
Sam
Please post some of the raw data.
you want to use the DateDiff function:
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
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
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
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
Thanks again
Sam
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;
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:
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
The trick is to make sure the @GameTImeStart is a datetime data type (in SQL Server).
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.
Building up to the working solution.
Thanks for contributing to this solution and for your recommendation for doing this right.
Kind Regards
Sam
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
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
Thanks for contributing to this solution and for your recommendation for doing this right.
Kind Regards
Sam
ASKER
dsacker,
See above, do you have a recommendation for "Best Practices"? This seems overly complicated.
Regards
Sam
See above, do you have a recommendation for "Best Practices"? This seems overly complicated.
Regards
Sam
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Thanks much for all your help on this. I hope it assists others.
Please look at my other issue # 40862921 .
Kind Regards
Sam