Dates in Variable

  • 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

  • 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

  • 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