Set variable in SELECT statement

Question

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

Solution

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

Comments

[user247702]

A little more complicated than what I was hoping for, but working. Thank you. Also, I think the X and the end of those queries is meant to be a D.