Here is a simple example. First create a stored procedure in your database
CREATE PROCEDURE dbo.GetDateName
GONow use that in a report as the data set. You will have a parameter @date, for which you can select the date and the report will show the weekday name.
Now replace the contents of the stored proc with what your actual query is.