Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATEFIRST setting oddity


DATEFIRST setting oddity

Author
Message
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6331 Visits: 7660
Alright, Specs time: Using SQL Server 2k5 version: 9.00.3042.00

I was messing around with DATEFIRST settings trying to determine if I could end up with a very tight first day of week function. Along the way I found a strangeness in the weekcount function, depending on the DATEFIRST setting.

First off, this is the code I'm running. Run it only on your local server or dedicated Dev environment. It manipulates DATEFIRST and you might botch something up on your real servers if you run it. Don't worry, it puts the setting back to where you found it, but still, keep it off public servers.


SET NOCOUNT ON

DECLARE @MyDateFirst INT

SET @MyDateFirst = @@DATEFIRST

DECLARE @C INT
SET @c = 1

WHILE @c < 8
BEGIN
--EXEC sp_executeSQL N'SET DATEFIRST ' + CAST( @c AS nVARCHAR(4))
SET DATEFIRST @c

SELECT
CONVERT( VARCHAR(10), GETDATE(), 101) AS DayInQuestion,
@c AS DateFirst_Set_To,
DATEPART(ww, getdate()) AS WeekNum,
DATEPART( dw, GETDATE()) AS DayOfWeekNum,
DATENAME( dw, DATEADD( yy, DATEDIFF( yy, 0, GETDATE()), 0)) AS NameOfDayOfWeekOfFirstOfYear

SET @c = @c + 1

END

SET DATEFIRST @MyDateFirst

SET NOCOUNT OFF



I end up with this:
DayInQuestion DateFirst_Set_To WeekNum     DayOfWeekNum NameOfDayOfWeekOfFirstOfYear
------------- ---------------- ----------- ------------ ------------------------------
11/03/2010 1 45 3 Friday
11/03/2010 2 45 2 Friday
11/03/2010 3 45 1 Friday
11/03/2010 4 44 7 Friday
11/03/2010 5 44 6 Friday
11/03/2010 6 45 5 Friday
11/03/2010 7 45 4 Friday


Now, here's my understanding of DATEPART(WK, DT). It is the number of weeks since the beginning of the year, with your DATEPART controlling the wk thresholds, and with the first day of the year always falling in WK 1. Simple enough, but I was confused by DATEPART = 6 setting (Friday).

Now, DATEFIRST goes 1 is Sunday, 2 is Monday... 7 is Saturday.

Now, this means @@DATEFIRST is actually @@DATELAST. To prove it to yourself, check out the results of this:

SET NOCOUNT ON

DECLARE @MyDateFirst INT

SET @MyDateFirst = @@DATEFIRST

DECLARE @C INT
SET @c = 1

WHILE @c < 8
BEGIN
--EXEC sp_executeSQL N'SET DATEFIRST ' + CAST( @c AS nVARCHAR(4))
SET DATEFIRST @c

SELECT
CONVERT( VARCHAR(12), DATEADD( dd, N-1, '1/1/2010')) AS DayInQuestion,
@c AS DateFirst_Set_To,
DATEPART(ww, DATEADD( dd, N-1, '1/1/2010')) AS WeekNum,
DATEPART( dw, DATEADD( dd, N-1, '1/1/2010')) AS DayOfWeekNum,
DATENAME( dw, DATEADD( dd, N-1, '1/1/2010')) AS DayOfWeekName,
DATENAME( dw, DATEADD( yy, DATEDIFF( yy, 0, GETDATE()), 0)) AS NameOfDayOfWeekOfFirstOfYear
FROM
tempdb..Tally
WHERE
N <= 7

SET @c = @c + 1

END

SET DATEFIRST @MyDateFirst

SET NOCOUNT OFF



I'll grab one loop's worth:

DayInQuestion DateFirst_Set_To WeekNum DayOfWeekNum DayOfWeekName NameOfDayOfWeekOfFirstOfYear
------------- ---------------- ----------- ------------ ------------------------------ ------------------------------
Jan 1 2010 1 1 5 Friday Friday
Jan 2 2010 1 1 6 Saturday Friday
Jan 3 2010 1 1 7 Sunday Friday
Jan 4 2010 1 2 1 Monday Friday
Jan 5 2010 1 2 2 Tuesday Friday
Jan 6 2010 1 2 3 Wednesday Friday
Jan 7 2010 1 2 4 Thursday Friday



So, Our @@DATEFIRST is 1, for Sunday. Note here that Sunday falls into week *1*, not *2*. It is the last day of the current week, not the beginning of the next.

Am I missing a setting somewhere or has this just had me fooled this long?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3794 Visits: 11771
What you are missing is the following from SQL Server Books Online under the SET DATEFIRST topic:
Value   First day of the week is  
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday

When you have @@DATEFIRST = 1, the first day of the week is Monday, not Sunday.

Also, DATEFIRST is a session setting, so it has no impact on other users or sessions on the same server. The initial value of DATEFIRST is the default value for the login's default language, so for US english it is 7.



I have a link to my own function for this, F_START_OF_WEEK, below. Note that the setting of DATEFIRST makes no difference to F_START_OF_WEEK; you just pass the week start date to the function.

Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307


A companion function F_END_OF_WEEK, posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759


Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6331 Visits: 7660
<------------- Okay, I earned one of these...

Michael Valentine Jones (11/3/2010)
What you are missing is the following from SQL Server Books Online under the SET DATEFIRST topic:
When you have @@DATEFIRST = 1, the first day of the week is Monday, not Sunday.


Blush That's what I get for not doublechecking someone's blog against the BOL. Thank you.


Also, DATEFIRST is a session setting, so it has no impact on other users or sessions on the same server. The initial value of DATEFIRST is the default value for the login's default language, so for US english it is 7.


I'll be over here in the corner, trying NOT to look like the local idiot. And failing. Thank you for that. I knew that and pulled a moron because I couldn't remember if some of them were connection level or not (like the transaction level ones are)... which wouldn't matter in your own query analyzer window anyway.

(EDIT: Before I get called on transaction level not being connection setting in 2k8, it depends on version: http://msdn.microsoft.com/en-us/library/aa259216(SQL.80).aspx. Much of my knowledge comes from 2k.)


I have a link to my own function for this, F_START_OF_WEEK, below. Note that the setting of DATEFIRST makes no difference to F_START_OF_WEEK; you just pass the week start date to the function.


Yeah, I was trying to test mine to make sure I didn't muck up a different datefirst. See how well that went? :-D


Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

A companion function F_END_OF_WEEK, posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

There are other Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

There are other End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762


Thanks for these, I'll check them out.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3794 Visits: 11771
It’s interesting that you brought up transaction isolation level.

There is a nasty gotcha to watch out for if your application is doing connection pooling: The transaction isolation level does not get reset by sp_reset_connection, so the transaction isolation level carries over to the next time the connection is reused. So if you run a serializable transaction the transaction isolation level will still be serializable the next time the connection is used unless it is specifically changed.

I spent a lot of time tracking that down before I realized what was going on. Microsoft has stated that this is not a bug and the behavior is intentional.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6331 Visits: 7660
Michael Valentine Jones (11/4/2010)

There is a nasty gotcha to watch out for if your application is doing connection pooling: The transaction isolation level does not get reset by sp_reset_connection, so the transaction isolation level carries over to the next time the connection is reused. So if you run a serializable transaction the transaction isolation level will still be serializable the next time the connection is used unless it is specifically changed.


Yep, and IIS almost always does it, thus my usual reaction to SET commands is based on the exception instead of the rule. I just got a bit carried away earlier. :-)

This fact is what got me away from running SET ISOLATION levels at all and using almost pure join hints in my structures. No matter what was going on, I could make sure, when I needed it, that my queries did exactly what I wanted them to.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52082 Visits: 40319
Craig Farrell (11/3/2010)
It manipulates DATEFIRST and you might botch something up on your real servers if you run it.


Setting DATEFIRST in a query only affects the session, not the entire server... which is also why I don't understand why people are afraid to write queries that rely on a particular @@DATEFIRST query.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6331 Visits: 7660
Jeff Moden (11/4/2010)
Craig Farrell (11/3/2010)
It manipulates DATEFIRST and you might botch something up on your real servers if you run it.


Setting DATEFIRST in a query only affects the session, not the entire server... which is also why I don't understand why people are afraid to write queries that rely on a particular @@DATEFIRST query.


Yeaaah, got called on that one already up above. Hehe I was a bit over-exuberant in making sure some poor soul didn't start mucking himself up with my code.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
LutzM
LutzM
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7611 Visits: 13559
I'm not sure if you're looking for a "static" function (in the meaning of returning the first day of a week regardless of the DATEFIRST setting)...

In this case you could use
SELECT DATEADD(wk,DATEDIFF(wk,0,YourDateValue),0)


This will always return the date for Monday of the week in question. However, it will start changing to the next week on Sunday. I you want to change the day to start a new week you'd need to subtract the "offset" to Sunday. For example, if you want to have Sunday belonging to the previous week and start with Monday, you'd need to subtract 1.
SELECT DATEADD(wk,DATEDIFF(wk,0,YourDateValue - 1),0)

Or are you looking for a fast ISO week calculation?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6331 Visits: 7660
Hey Lutz.

Yeah, found the 'Monday' equation, which is how I think I went flying up the wrong DATEFIRST when I did a little research behind it on the web. I misread something completely and it went downhill from there.

Basically, what I was looking to figure out was a tight datediff/add calculation that could deal with week groupings dependent on datefirst, while also being able to display the date of the first day of the week based on datefirst. The weeks were easy enough (though I'm still working out the new/old year crossover), but when I went exploring the proper datefirst value subtractions I started on my tangent. Smile

I found one calculation that uses a %7 that I'm not sure is precise, but I'll see what I can bend out of that once I get a chance to look through the linked ones above.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search