DATEFIRST setting oddity

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » DATEFIRST setting oddity

DATEFIRST setting oddity

Rate Topic

Display Mode

Topic Options

Author

Message

Evil Kraig F

Posted Wednesday, November 03, 2010 6:18 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

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

Post #1015638

Michael Valentine Jones

Posted Wednesday, November 03, 2010 8:59 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 2,944, Visits: 10,510

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

Post #1015665

Evil Kraig F

Posted Wednesday, November 03, 2010 10:44 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

<------------- 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.

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? BigGrin

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.

Post #1015677

Michael Valentine Jones

Posted Thursday, November 04, 2010 8:23 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 2,944, Visits: 10,510

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.

Post #1015912

Evil Kraig F

Posted Thursday, November 04, 2010 12:32 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

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. Smile

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.

Post #1016117

Jeff Moden

Posted Thursday, November 04, 2010 6:59 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1016332

Evil Kraig F

Posted Thursday, November 04, 2010 9:34 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

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.

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.

Post #1016380

LutzM

Posted Friday, November 05, 2010 11:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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

Post #1016692

Evil Kraig F

Posted Friday, November 05, 2010 11:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 AM
Points: 5,678, Visits: 6,126

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. :)

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.

Post #1016703

« Prev Topic | Next Topic »

Permissions