• check system function datepart in BOL.

    select datepart(dw, getdate())

    returns today's weekday.