September 24, 2013 at 10:26 am
Hello. I need to be able to group data based on not only the date, but also the "week of". However, the "week" is defined as Wed - Tues.
Basically, I think what I need is for the code to convert a date to the preceding Wednesday (not the Wed of last week). For example, 9/23/2013 would convert to 9/18/2013, but 9/27/2013 would convert to 9/25/2013.
I can find some code to find a date in the previous week, but nothing like this (so far).
I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.
Thanks,
PK
September 24, 2013 at 11:02 am
Taking a crack at this... Try this article:
With a tally table, you could whip up something like this; note that my naming is a bit sketchy, because this is going to be mostly a test of concept:
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N INTO #Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE #Tally ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
SELECT N,CONVERT(DateTime,32500+N) AS Dates
INTO #Temp
FROM #Tally
ORDER BY 32500+N ASC
SELECT N,CONVERT(DateTime,32500+N) AS Dates
INTO #Temp2
FROM #Tally
SELECT b.Dates,a.Dates FROM #Temp2 a
INNER JOIN #Temp b
ON a.N >= B.N AND a.N < b.N+7
WHERE DATEPART(DW,b.Dates) = 4
ORDER BY b.Dates ASC
This is going to give you a set of dates from 1988 to 2019, and a pair of columns; one will have the Wednesday of a particular week (your "starting point", in essence), and the other will contain all of the dates that would fall into this week. The dates will "belong" to the week on the left; in essence, you'd run an update from here to set the actual date to the "starting point" date.
This is a rough conceptual example, since we don't have sample data and so forth; this code will probably need a good bit of adaptation, but it should be a good starting point. Please include table definitions and sample data if you'd like more clarification.
- 😀
September 25, 2013 at 9:01 am
A user on another forum suggested this:
DATEADD(dd,-DATEDIFF(dd,2, YourDateColumn)%7,CAST(YourDateColumn AS DATE))
DATEDIFF(dd,2,YourDateColumn) calculates the number of days between the value in YourDateColumn and the date represented by the number 2 - which is Jan 3, 1900 - which happened to be a Wednesday.
So DATEDIFF(dd,2,YourDateColumn)%7 will be zero if YourDateColumn is a Wednesday, 1 if it is Thursday, 2 if it is Friday and so on.
When you subtract 0 days from Wed, or 1 day from Thursday, or 2 days from Friday and so on, you get to Wednesday. Hence the "-DATEDIFF(dd,2,YourDateColumn)%7".
September 25, 2013 at 11:22 am
paul.j.kemna (9/24/2013)
I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.
I believe the best thing to do would be to add WeekStartWednesday and DayOfWeekWednesday to your calendar table. It also sounds like you may have to change a wad of code whether you can make this Calendar table change or not.
I'd also be interested in the rules behind determining what the first and last week of any given year would be.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2013 at 1:18 pm
We don't own the calendar table, so that is not really an option.
Anyway, my boss and I came up with this code as an option as well:
DECLARE @someDate as DATE = '2013-09-26'
select CASE WHEN DATEPART(DW, @someDate) >= 4 THEN DATEADD(d,4-DATEPART(DW, @someDate), @someDate) ELSE
DATEADD(d,4-DATEPART(DW, DATEADD(D, -7, @someDate)),DATEADD(D, -7, @someDate)) END
Weeks of the year is not necessary in this instance. We simply needed to be able to group by a week start of Wednesday, and display the past X weeks. (I chose 8 as a place to start).
PK
September 25, 2013 at 4:26 pm
paul.j.kemna (9/25/2013)
We don't own the calendar table, so that is not really an option.Anyway, my boss and I came up with this code as an option as well:
DECLARE @someDate as DATE = '2013-09-26'
select CASE WHEN DATEPART(DW, @someDate) >= 4 THEN DATEADD(d,4-DATEPART(DW, @someDate), @someDate) ELSE
DATEADD(d,4-DATEPART(DW, DATEADD(D, -7, @someDate)),DATEADD(D, -7, @someDate)) END
Weeks of the year is not necessary in this instance. We simply needed to be able to group by a week start of Wednesday, and display the past X weeks. (I chose 8 as a place to start).
PK
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.
Try this to return the datetime of the Wednesday on or before @someDate at time 00:00:00.000 (midnight). You can cast the result back to DATE if you prefer.
declare @someDate as DATE = '2013-09-26'
Select Wed = dateadd(dd,((datediff(dd,'17530103',@someDate)/7)*7),'17530103')
More info here:
Start of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Edit: Modified code to use parameter of type DATE
September 26, 2013 at 6:59 am
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.
Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2013 at 9:25 am
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.
I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice. 😎
I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.
People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.
September 26, 2013 at 5:38 pm
Michael Valentine Jones (9/26/2013)
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.
I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice. 😎
I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.
People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.
Absolutely agreed across all points especially the part about it not being any more difficult. I was actually talking about putting SET DATEFIRST into the actual code, though. If someone can't figure out the integer math, I can't see why you couldn't use SET DATEFIRST for the session. People seem almost phobic about its use.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2013 at 5:46 am
Isn't SET DATEFIRST a sever wide setting?
September 27, 2013 at 6:23 am
paul.j.kemna (9/27/2013)
Isn't SET DATEFIRST a sever wide setting?
Nope. Only affects the session. I've been developing code to create and update a master calendar table for our organization so I've been hip-deep in dates this past week.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
September 27, 2013 at 8:06 am
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/26/2013)
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.
I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice. 😎
I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.
People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.
Absolutely agreed across all points especially the part about it not being any more difficult. I was actually talking about putting SET DATEFIRST into the actual code, though. If someone can't figure out the integer math, I can't see why you couldn't use SET DATEFIRST for the session. People seem almost phobic about its use.
If you set DATEFIRST to something other than the expected value, there could be downstream issues in the next procedure or code that gets executed if it depends on the setting of DATEFIRST. Or if your code depends on the setting of DATEFIRST, you don't specifically set DATEFIRST, and upstream code has modified DATEFIRST to an unexpected value.
If you are going to use DATEFIRST, it is probably best to set it specifically and then reset it to the default if you are setting it to a non-default value.
If you are using connection pooling, I believe that the value of DATEFIRST does get reset to the default by the API stored procedure sp_reset_connection (unlike the isolation level :angry: )
set nocount on
set datefirst 7 -- Normal US English setting
select DW1 = datepart(dw,'20130927')
go
set datefirst 3 -- Set to Wednesday
select DW2 = datepart(dw,'20130927')
go
exec ('select DW3 = datepart(dw,''20130927'')')
Results:
DW1
-----------
6
DW2
-----------
3
DW3
-----------
3
September 27, 2013 at 9:38 am
Why mess with the DATEFIRST setting when it's not necessary? There are methods which can do the calculation simply without needing a specific datefirst setting, so why hassle with it and take chances?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 27, 2013 at 3:58 pm
ScottPletcher (9/27/2013)
Why mess with the DATEFIRST setting when it's not necessary? There are methods which can do the calculation simply without needing a specific datefirst setting, so why hassle with it and take chances?
Because, as strange as it sounds, some people just can't do the math on those simple methods.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2013 at 3:59 pm
Michael Valentine Jones (9/27/2013)
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/26/2013)
Jeff Moden (9/26/2013)
Michael Valentine Jones (9/25/2013)
That's not really the best way to go because it depends on the setting of a run-time session parameter, DATEFIRST.Although I certainly prefer you method, I have to ask... why does everyone have such a problem with that? It's no different than setting a variable that would be used as a constant. The only time that such a setting change would be a problem is if it were in a function, which can't be done TTBOMK.
I wanted the OP to be aware of the limitations of their method. They are certainly free to ignore my wonderful advice. 😎
I prefer to have code that works as expected under the widest possible conditions, especially if it's no harder to code.
People may be operating with different default languages than our "normal" US English, and that can change the setting of DATEFIRST, and the client application can also define that setting in an unexpected way.
Absolutely agreed across all points especially the part about it not being any more difficult. I was actually talking about putting SET DATEFIRST into the actual code, though. If someone can't figure out the integer math, I can't see why you couldn't use SET DATEFIRST for the session. People seem almost phobic about its use.
If you set DATEFIRST to something other than the expected value, there could be downstream issues in the next procedure or code that gets executed if it depends on the setting of DATEFIRST. Or if your code depends on the setting of DATEFIRST, you don't specifically set DATEFIRST, and upstream code has modified DATEFIRST to an unexpected value.
If you are going to use DATEFIRST, it is probably best to set it specifically and then reset it to the default if you are setting it to a non-default value.
If you are using connection pooling, I believe that the value of DATEFIRST does get reset to the default by the API stored procedure sp_reset_connection (unlike the isolation level :angry: )
set nocount on
set datefirst 7 -- Normal US English setting
select DW1 = datepart(dw,'20130927')
go
set datefirst 3 -- Set to Wednesday
select DW2 = datepart(dw,'20130927')
go
exec ('select DW3 = datepart(dw,''20130927'')')
Results:
DW1-----------
6
DW2
-----------
3
DW3
-----------
3
Thanks, Michael. I keep forgetting about connection pooling. That's a REALLY good reason to not use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply