1

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

enter image description here

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • 2
    I don't see that result in the docs. The docs show what the _meaning_ of various values of `DATEFIRST` is. You have it at 7, so `DATEPART` considers (as the table in the docs illustrates) Sunday to be the first day of the week. So your Sunday gets a `weekday` of 1 – AakashM Apr 06 '22 at 07:40
  • 2
    This result expects `SET DATEFIRST 1`. – Zhorov Apr 06 '22 at 07:40
  • @AakashM I disagree, I clearly see in the docs a table with my expected results, and it clearly states in the last line that the value for @@datefirst is 7 which is the default – GuidoG Apr 06 '22 at 07:42
  • @Zhorov See my comment above – GuidoG Apr 06 '22 at 07:42
  • Just to confirm, when you look at the docs you see a page with one table, preceded by the text "Is an integer that indicates the first day of the week. It can be one of the following values." ? It is talking about **what the setting of `DATEFIRST` means**. It is *not* talking about 'what `DATEPART(weekday` returns for various days or settings' – AakashM Apr 06 '22 at 07:44
  • @AakashM I added a screenshot from the docs in my question – GuidoG Apr 06 '22 at 07:44
  • note the header of the second column "first day of the week is". So if you select 7, the first day of the week is Sunday. If you select 1, you will get monday – Dugnom Apr 06 '22 at 07:45
  • I seem to read the documentation different than you guys, hence my confusion. English is not my mother tongue, but I find this documentation confusing some how – GuidoG Apr 06 '22 at 07:48
  • 2
    Perhaps the documentation of [DATEPART](https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15) helps. It shows a different table. It is confusing; you set Sunday (Day 7) as Day 1 – HoneyBadger Apr 06 '22 at 07:49
  • @HoneyBadger Yes that helps understand it better, thank you – GuidoG Apr 06 '22 at 07:50

1 Answers1

5

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.

AakashM
  • 62,551
  • 17
  • 151
  • 186
  • OK I understand my mistake now, I should not read this in the morning when not compete awake yet :) – GuidoG Apr 06 '22 at 07:51
  • 1
    Numbers are, in my opinion, the logical choice. Other options, like the name of the day, would be language specific, and so would not be able to be coded against easily. If `@@DATEFIRST = N'Monday'` won't work if your language is set to `FRENCH`. – Thom A Apr 06 '22 at 07:51
  • @Larnu Yes, I totally agree, that is why I wanted to use numbers, but then I got into the @@datefirst setting where the documentation confused me. – GuidoG Apr 06 '22 at 07:52
  • `@@DATEFIRST` isn't a setting though, @Guido, it's a session based (environmental) variable. – Thom A Apr 06 '22 at 07:53
  • @Larnu hm, from the docs `To see the current setting of SET DATEFIRST, use the @@DATEFIRST function.` – GuidoG Apr 06 '22 at 07:54
  • @Larnu So the confusion continues :) – GuidoG Apr 06 '22 at 07:54
  • 1
    I wouldn't call it a "function" personally, but that doesn't make it a setting. `@@DATEFIRST` *returns* a setting's value, but it isn't a setting itself. The setting is `DATEFIRST` (no leading @ characters). – Thom A Apr 06 '22 at 07:54
  • @GuidoG if you sent SET DATE FIRST 1; you can get the results as documentation, look like the default value is 7 – Asela Sampath Apr 06 '22 at 07:56