There are functions which already provided by SQL. We will use these function to achieve our expected result.
To get todays day name using system datetime ,we can use following query.
Demo and Full Guide
how to get todays date in sql how to get todays date in sql server get today's date in sql server sql get today at midnight sql get today date without time sql get current day sql get current day of week sysdate in sql server