Dynamically choosing column for WHERE clause
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()))