returns an integer of the day of week. 1..7.
See Also: DAY, FORMAT
/* Retrieve list of weekdays with weekday name. */
/* Be aware that the day name may be differ based on culture of the machine you’re running this script on. */
/* E.g. Sunday may be day #1 */
SELECT DISTINCT thedate,DAYOFWEEK(thedate) WeekDayNo,
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END AS WeekDayName