September 21, 2011 at 8:12 pm
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
September 21, 2011 at 8:50 pm
ringovski (9/21/2011)
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
So if your duration is in hours, it seems you just need to add those hours to the starttime
So you could do:
select dateadd(hour,duration, starttime) from shift
So if you have a where clause so this is only going to return one value you could do:
declare @calcduration datetime
select @calcduration = dateadd(hour,duration, starttime) from shift
where "put your where clause here"
Hope that helps.
Ben
September 22, 2011 at 8:46 am
Unless you are trying to get the results for more than 1 row. You can't store multiple results in a single variable. If that is what you are trying to do you could do something like this:
SELECT dateadd(hour,duration, starttime)
into #DateTimes
FROM Shift
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply