Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATEFIRST setting oddity Expand / Collapse
Author
Message
Posted Wednesday, November 3, 2010 6:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Posted Wednesday, November 3, 2010 8:59 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 3,113, Visits: 11,540
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
Posted Wednesday, November 3, 2010 10:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
<------------- 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?


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
Post #1015677
Posted Thursday, November 4, 2010 8:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 3,113, Visits: 11,540
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
Posted Thursday, November 4, 2010 12:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Post #1016117
Posted Thursday, November 4, 2010 6:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 35,769, Visits: 32,439
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1016332
Posted Thursday, November 4, 2010 9:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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. 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
Post #1016380
Posted Friday, November 5, 2010 11:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:16 PM
Points: 6,847, Visits: 13,387
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
Posted Friday, November 5, 2010 11:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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.



- 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 #1016703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse