Date in Variable

[Ringers]

Hi,

I am trying to add two columns from table SHIFT. Column DURATION(INT) to COLUMN STARTTIME(datetime) and then put the result in a variable.

1. Convert DURATION to hours;

SELECT CONVERT(varchar, DATEADD(SS,Duration, 0), 114)+ StartTime
FROM Shift

2. Add results into variable, not sure how to do this. I have tried the following;

DECLARE @CalcDuration DateTime
SELECT @CalcDuration=
(SELECT CONVERT(varchar, DATEADD(SS,Duration, 0), 114)+ StartTime
FROM Shift
)
------------------------------------------------
DECLARE @CalcDuration DateTime
SET @CalcDuration = CONVERT(varchar, DATEADD(SS,Duration, 0), 114)+ StartTime
FROM SHIFT

Thanks for any help

 

[markros]

Can you show some samples of the data and desired output?
With a single variable you can only get 1 record's info.

So, you can do

select top (1) @CalcDuration = dateadd(second, Duration, StartTime) -- assuming duration is in seconds
from Shift

PluralSight Learning Library

 

[Ringers]

Duration(INT as minutes)
210
210
210
1440
1440

StartTime(datetime)
1900-01-01 09:00:00.000
1900-01-01 09:00:00.000
1900-01-01 09:00:00.000
1900-01-01 09:00:00.000
1900-01-01 09:00:00.000

expected results
1900-01-01 12:30:00.000 as 210 minutes is 3.5 hours.

Thanks

 

[Ringers]

looks like my statements are wrong the conversion statement doesn't work either.

SELECT CONVERT(int, DATEADD(hh,Duration, 0), 114)+ StartTime
FROM Shift

 

[starsky51]

DECLARE @datetable TABLE (startdate datetime
						, durationmins int)
						
INSERT INTO @datetable SELECT '1900-01-01 09:00:00', 210
			 UNION ALL SELECT '1900-01-01 09:00:00', 1440
			 
SELECT startdate
	 , durationmins
	 , DATEADD(minute, durationmins, startdate) AS enddate
FROM @datetable