SELECT DATEPART(weekday, getdate())This gives you a number from 1 to 7, corresponding to the day in the week.
But which day is the first day in the week?
If you are European - it's Monday.
If you are American - it's Sunday.
If you are SQL Server - it depends...
There's a setting in SQL Server that controls this behavior and perhaps it is not set to your liking. And perhaps you don't want to change it and run the risk of side-effects - or perhaps you simply don't have the permissions.
This simple code will allow you to get the day in week independent of SQL Server's settings:
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 1) % 7
SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 2) % 7