I have read this documentation
So I tried this experiment
declare @t table (test date)
insert into @t values ('20220404'), ('20220405'),('20220406'),('20220407'),('20220408'),('20220409'),('20220410')
select datename(weekday, test),
datepart(weekday, test)
from @t
it returns this
| COLUMN1 | COLUMN2 |
|---|---|
| Monday | 2 |
| Tuesday | 3 |
| Wednesday | 4 |
| Thursday | 5 |
| Friday | 6 |
| Saturday | 7 |
| Sunday | 1 |
I checked my value or @@DATEFIRST
select @@DATEFIRST
it returns 7
So why do I not get this result then as described in the docs?
| COLUMN1 | COLUMN2 |
|---|---|
| Monday | 1 |
| Tuesday | 2 |
| Wednesday | 3 |
| Thursday | 4 |
| Friday | 5 |
| Saturday | 6 |
| Sunday | 7 |
EDIT
this is what I see in the docs
>Solution :
I think you may be misunderstanding the docs. The docs for DATEFIRST say, as you’ve seen:
Sets the first day of the week
So, the value of DATEFIRST determines which day gets numbered 1, the first day of the week. With DATEFIRST set to 7, as the table goes on to show, Sunday will be considered the first day of the week – day number 1.
With that setting, DATEPART for weekday will return 1 for any Sunday, because Sunday is considered the first day of the week.
It is perhaps unfortunate that numbers are used as the argument to SET DATEFIRST, since naturally this confusion arises. It might have been nice if we could say SET DATEFIRST Sunday to make it obvious what we mean, but unfortunately that’s not the syntax.
