Dynamically choosing column for WHERE clause I've got a table that holds columns named "Day1" through to "Day31". 

Is there a way I can dynamically choose the correct column to put in a WHERE clause?

I can get the current day, that's no problem, but I don't know where to go from there.
Use DATEPART DAY to retrieve today's day, then build a simple dynamic SQL concatenating that day's value to the column name.

IF OBJECT_ID('tempdb..#PivotedInfo') IS NOT NULL
    DROP TABLE #PivotedInfo

CREATE TABLE #PivotedInfo (
    Day1 INT,
    Day2 INT,
    Day3 INT,
    Day4 INT)

DECLARE @DayToday INT = DATEPART(DAY, GETDATE())

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '


PRINT (@DynamicSQL)

/*
    SELECT
        P.[Day3]
    FROM
        #PivotedInfo AS P 
*/


EXEC (@DynamicSQL)


If you need a general column name as result, you can add an alias:

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        DailyValue = P.[Day' + CONVERT(VARCHAR(2), @DayToday) + ']
    FROM
        #PivotedInfo AS P '


PRINT (@DynamicSQL)

/*
    SELECT
        DailyValue = P.[Day3]
    FROM
        #PivotedInfo AS P 
*/




You can avoid using dynamic SQL if you unpivot your columns and then filter where the unpivot column name matches your wanted day. It would be more readable but more expensive operation.

INSERT INTO #PivotedInfo (Day1, Day2, Day3, Day4)
VALUES (5, 10, 15, 20)

SELECT
    P.*
FROM
    #PivotedInfo AS I
    UNPIVOT (
        DailyValue FOR Day IN  ([Day1], [Day2], [Day3], [Day4]) -- All day list here
    ) AS P
WHERE
    P.Day = 'Day' + CONVERT(VARCHAR(2), DAY(GETDATE()))