Shift week to Wed - Tues

  • 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

  • Taking a crack at this... Try this article:

    Tally Tables[/url]

    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.

    - 😀

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

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Isn't SET DATEFIRST a sever wide setting?

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

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply