0

Suppose I would do this in a query : 

set DATEFIRST 1  --Monday

that will explicitly set the day of week to be regarded as the 'first'

But will this be the case for this transaction only, or will it be for all queries I run from this point on ?
That part is not mentioned in the documentation. At least I could not find it in this documentation docs

I am using sql server 2014

The answer in the dupe question helps alot. It explains the value is per session.
But another question pops up, when dotnet reuses the connection with its pooling mechanism, will this setting survive or not ?
I could not find that in the other question.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • your session ends when you close the connecton,normally one connection will have one session – TheGameiswar Aug 14 '17 at 07:54
  • My connection is always build in a using statement, but dotnet reuses it with its pooling mechanism right ? So I am not sure than how long this value of datefirst persists than – GuidoG Aug 14 '17 at 07:56
  • it works until ,you use connection.close or connection is cloded – TheGameiswar Aug 14 '17 at 07:57
  • 1
    @GuidoG - I *believe* that the magical `sp_reset_connection` that is run when a connection is reused from the pool resets the `DATEFIRST` setting, but I haven't checked for that specifically. – Damien_The_Unbeliever Aug 14 '17 at 08:01
  • @Damien_The_Unbeliever OK I can look that up thank you. But I am going for the solution in your answer that is simple and safe – GuidoG Aug 14 '17 at 08:03

2 Answers2

2

It applies to your session/connection - so long as you maintain the same connection, all queries executed on it will make use of the current DATEFIRST setting. It outlives any particular transaction or batch.

However, I'd usually recommend against changing this setting. If possible, instead, find ways to express your queries in a manner that always works, no matter what setting is in effect.

For instance, if I want to have a predicate to test whether a particular stored value is a Monday, I'd write something like:

DATEPART(weekday,<ValueToTest>) = DATEPART(weekday,'20151116') --"Known Good" Monday

The above always works, no matter what DATEFIRST setting is in effect.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

Easy enough to test. Setting set DATEFIRST n is only for the current session, and only needs to be declared once. Tested it now.

Koby Douek
  • 16,156
  • 19
  • 74
  • 103