Datepart and @@datefirst differences

Question

It is my understanding that DateFirst is the first day of the week, or the day the week starts on. However, when I run these queries, my results do not match what I thought I understood

select @@datefirst,DATENAME(WEEKDAY,@@datefirst)
SELECT DATEPART(WEEKDAY,'20190113'),DATENAME(WEEKDAY,'20190113')
SELECT DATEPART(WEEKDAY,'20190114'),DATENAME(WEEKDAY,'20190114')
SELECT DATEPART(WEEKDAY,'20190115'),DATENAME(WEEKDAY,'20190115')
SELECT DATEPART(WEEKDAY,'20190116'),DATENAME(WEEKDAY,'20190116')
SELECT DATEPART(WEEKDAY,'20190117'),DATENAME(WEEKDAY,'20190117')
SELECT DATEPART(WEEKDAY,'20190118'),DATENAME(WEEKDAY,'20190118')
SELECT DATEPART(WEEKDAY,'20190119'),DATENAME(WEEKDAY,'20190119');

and I get

7 Monday (this is the @@datefirst line)
1 Sunday
2 Monday
3 Tuesday
4 Wednesday
5 Thursday
6 Friday
7 Saturday

How does @@datefirst give me 7 and the day give me Monday, whereas 7 is Saturday? Is this a config issue or a misunderstanding on my part?

I think this can help you : https://stackoverflow.com/q/20031802/9940803 — Sanpas, Jan 14 '19 at 22:16
`DATENAME` expects a date as its parameter, so passing it `7` is actually passing it `7 Jan 1900` which I assume is a `Monday` — Dale K, Jan 14 '19 at 22:16
@DaleBurrell is referring to this part of your code if you aren't following `DATENAME(WEEKDAY,@@datefirst)`. You should add that as an ander Dale :) — S3S, Jan 14 '19 at 22:19
Its also worth noting that the value returned by `DATEPART` and `DATENAME` for `WEEKDAY` are relative to the value returned from `@@DATEFIRST`, which is absolute i.e. when `@@DATEFIRST` is `7` (always Sunday) `DATEPART` returns `7` which is Saturday (which had me confused). https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017 — Dale K, Jan 14 '19 at 22:41
@DaleBurrell in the following documentation they mentioned that if the input is not relative to the datepart specified it will return a default value. Check my answer for more info — Hadi, Jan 14 '19 at 22:45
Thanks @Hadi but my point is in addition to your answer and clarifies what happens when you use a valid date - as in this case the value of `DATEPART` is relative to `@@DATEFIRST` - see my link. Feel free to add this to your answer. — Dale K, Jan 14 '19 at 22:49
Aside: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. — HABO, Jan 16 '19 at 18:18

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Based on the following Microsoft Documentation:

This function returns a character string representing the specified datepart of the specified date.

Syntax: DATENAME ( datepart , date )

Update 1

In the same article, in the Section: Default Returned for a datepart That Is Not in the date Argument

If the data type of the date argument does not have the specified datepart, DATENAME will return the default for that datepart only if the date argument has a literal .

For example, the default year-month-day for any date data type is 1900-01-01. This statement has date part arguments for datepart, a time argument for date, and DATENAME returns 1900, January, 1, 1, Monday

So when you enter the number 7 the input is considered as 1900-01-01 07:00:00 which return Monday

If you go to SQL Server and Run the following command

select DATENAME(WEEKDAY,'1900-01-01 07:00:00')

It returns

Monday

iSR5 · Answer 2 · 2019-01-15T00:43:18.663

Your understanding of the @@DATEFIRST is correct.

The @@DATEFIRST is a function that returns the first day of the week and its default value depends on the system environment.

If you change for instance, the Language, the @@DATEFIRST default value will be changed according to that language settings. Although, @@DATEFIRST can be set manually without the need to change the language settings.

So, in your example results, Day number 7 (which is Sunday) is defined in your system as the first day of the week, however, when you use it in a date function (such as DATENAME() ), it'll give you an incorrect results, because @@DATEFIRST will return an integer (from 1 to 7) while the date functions takes a DATE or DATETIME inputs and not integers. Those, your method of using @@DATEFIRST is not efficient. @@DATEFIRST has multiple uses, one of these uses is to get the correct weekday.

Let's take a simple example. My current @@DATEFIRST settings is set to 7, and I want to make a scalar function that always gives me the date of the upcoming Sunday. So, I can use this :

SELECT DATEADD(DW, @@datefirst - DATEPART(DW, GETDATE()) + 1 , GETDATE())

This will always gives me the next Sunday from today. Let's say, one of my clients is in another country has different start of week (let's say Monday = 1). I don't have to change anything in the code above, I only need to execute the following :

SET DATEFIRST 1

Now, this will give my client the first Monday from the current date, without the need to do further customization on my actual query.

This is just a simple example that gives you an idea on the usage of @@DATEFIRST

You can check these references to know more about it.

@@DATEFIRST (Transact-SQL)

SET DATEFIRST (Transact-SQL)

Datepart and @@datefirst differences

2 Answers2

Update 1