CODEWITHSUNDEEP
sql-server-2008variablesselect
user247702
user247702

Reputation: 24232

Set variable in SELECT statement

I'm having the same problem as described here, unfortunately the solution does not work for MS SQL Server. Does a similar syntax exist for MS SQL Server?

Note: my query is not as simple as in the example. I'd like to reuse DifferenceMinutes in the TooLateTime case.

DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes,
CASE 
    WHEN DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) < 0 THEN NULL 
    ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
END AS TooLateTime

Upvotes: 2

Views: 20567

Answers (1)

Bennor McCarthy
Bennor McCarthy

Reputation: 11675

It's a little hard to tell exactly what you're trying to do, but I think this might be what you're looking for:

SELECT
    DifferenceMinutes,
    CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END AS TooLateTime
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

You'll have to substitute your source table(s) for "TableName" in the FROM section of the inner query.

By rolling your calculated values into a nested select like this, you can refer to them by whatever name you give them in the outer query.

If you want to set variables for each of the values, you can do that as follows, but you'll need to make sure you're only returning one row from the query:

DECLARE @DifferenceMinutes int, @TooLateTime varchar(30)
SELECT
    @DifferenceMinutes = DifferenceMinutes,
    @TooLateTime = CASE 
        WHEN DifferenceMinutes < 0 THEN NULL
        ELSE CONVERT(varchar, GETDATE() - DayOfWeekStopTime, 108) 
    END
FROM (
    SELECT
        DayOfWeekStopTime,
        DATEDIFF(MINUTE, DayOfWeekStopTime, GETDATE()) AS DifferenceMinutes
    FROM TableName
) X

Upvotes: 4

Related Questions