This surprises me that someone has not already posted this.
The TD_Day_of_Week function returns an integer value that need to converted to string value. Careful when using this on a large data set, it can really slow down the response time.
select TD_Day_of_Week(current_date) AS "TD_Day_of_Week Value", CASE TD_Day_of_Week(current_date) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Satday' END AS "Day of the Week"
Note: If you know of a native Teradata function that does this, please leave me note in the comments.