Datepart and @@datefirst differences

user_9655663 0
Viewed 436

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?

date tsql sql-server sql
2 Answers

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
user_70312300

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)

user_23220990
Related