DATEFIRST

Question

DATEFIRST

mo

Valued Member

Points: 64
More actions
May 28, 2020 at 1:42 pm

#3757997

hi
Is there a way to SET DATEFIRST server or database wide? I need Saturday be first day of the week so in every proc I set datefirst but in views I cannot do that.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736281 More actions · Answer 1

This is based on your language setting.

SELECT top 10
 *
 FROM sys.syslanguages

There is a datefirst column here. If you change the language, you change this.

Apart from that, I'm not sure you can do this globally. You can likely set defaults for sessions and for connections, but that would be client by client.

Can I ask the use case?

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I prefer not to depend on DATEFIRST settings in my code.

There are several pretty simple techniques to make your code independent of it.

For example, using system variable @@DATEFIRST in your code.

Or relying on the fact that zero date in SQL Server is Monday, so date number 5 is Saturday.

Therefore any day which is whole number of weeks away from day 5 is also Saturday.

Or you may use a Calendar table, which might solve more than one of your problems.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

Steve Jones - SSC Editor wrote:

This is based on your language setting.
SELECT top 10
 *
 FROM sys.syslanguages
There is a datefirst column here. If you change the language, you change this.
Apart from that, I'm not sure you can do this globally. You can likely set defaults for sessions and for connections, but that would be client by client.
Can I ask the use case?

Just not for a language that starts on Saturday (or any day other than Sunday or Monday) 😉

 SELECT DISTINCT (datefirst)
   FROM sys.syslanguages
;

Results:

I totally agree with the methods that Sergiy stated. I also wish people would all switch to ISO standards.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)