Get the number of the current day of the week in SQL Server

In SQL Server there is a built-in function called Datepart() which is takes 2 paramaters which are return date option and date value.
for the 1st paramater pass the date option as 'dw' and for second parameter pass the date value as shown below,

SET dateformat dmy Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek

if you execute this query, output will be,


in SQL Server, by default the week start with 'Monday' which is 1. so in this example, the week is Tuesday. So the number of the Tuesday is 2.
You can check, what is default start week number by using @@DATEFIRST.

Select @@DATEFIRST

Since SQL Server default start week number is 1(Monday), it is giving 1 in output.
Default Value for Week in SQL Server,

Monday - 1
Tuesday - 2
Wednesday - 3
Thursday - 4
Friday - 5
Saturday - 6
Sunday - 7

But You can change the default start week number as shown below,

SET datefirst 7

So here we make the start week number to 7 which is sunday.
After set the Datefirst to 7(sunday) and execute the first query you will get as shown below,

SET datefirst 7
SET dateformat dmy Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek

Now if you see the output, it shows 3. because now week start from Sunday instead of Monday. That is the reason why now week number became 3.