November 3, 2010 at 6:18 pm
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
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
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?
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 8:59 pm
What you are missing is the following from SQL Server Books Online under the SET DATEFIRST topic:
ValueFirst day of the week is
1Monday
2Tuesday
3Wednesday
4Thursday
5Friday
6Saturday
7Sunday
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:
November 3, 2010 at 10:44 pm
<------------- 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? 😀
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:
Thanks for these, I'll check them out.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 4, 2010 at 8:23 am
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.
November 4, 2010 at 12:32 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 4, 2010 at 6:59 pm
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
Change is inevitable... Change for the better is not.
November 4, 2010 at 9:34 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 5, 2010 at 11:37 am
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?
November 5, 2010 at 11:50 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply