Post reply

getting all records between 2 fridays of a given date

  • December 30, 2008 at 3:47 pm

    #129711

    I have a sql query in which I want to get all the records that lie between the previous friday and the coming friday of a given date.

    I want my query to look like this:

    select * from NoActivity

    WHERE (BranchNumber = '135'

    AND (WeekEndingDate lies between the previous friday and the coming friday of '12/18/2008' ))

    ie, the weekending date should be between '12/12/2008' and '12/19/2008'

    Can somebody help?

    I am kind of stuck halfway with this query.

    Thanks!

  • December 30, 2008 at 4:45 pm

    #919372

    SELECT * FROM NoActivity

    WHERE (BranchNumber = '135'

    AND (WeekEndingDate

    BETWEEN DATEADD(wk, DATEDIFF(wk,5,GETDATE())-1, 5)

    AND DATEADD(ss,86399,DATEADD(wk, DATEDIFF(wk,4,GETDATE()), 4))))

    For 12/30/08:

    DATEADD(wk, DATEDIFF(wk,5,GETDATE())-1, 5) = '2008-12-27 00:00:00.000'

    DATEADD(ss,86399,DATEADD(wk, DATEDIFF(wk,4,GETDATE()), 4)) = '2009-01-02 23:59:59.000'

    Hope this might help.

  • December 30, 2008 at 4:51 pm

    #919373

    thanks !!

    that was really helpful

  • December 30, 2008 at 9:20 pm

    #919423

    Um... any business conducted during that one second missing out of each week? 😉

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

  • December 30, 2008 at 9:28 pm

    #919427

    NULL has the right idea... just missing one second per week in the code. Using NULL's same idea, this is missing no seconds during the week...

    SELECT *

    FROM NoActivity

    WHERE BranchNumber = '135'

    AND WeekEndingDate >= DATEADD(wk,DATEDIFF(wk,5,GETDATE())-1,5)

    AND WeekEndingDate < DATEADD(wk,DATEDIFF(wk,4,GETDATE()),4)+1

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

  • December 30, 2008 at 11:39 pm

    #919456

    And Jeff's post is better formatted also 😛

    Random Technical Stuff[/url]

  • December 31, 2008 at 5:52 am

    #919567

    ta.bu.shi.da.yu (12/30/2008)

    And Jeff's post is better formatted also 😛

    Heh... 2nd formula turned out way shorter than the original... I get lucky like that.

    NULL's format is a whole lot better than what I've seen a lot of folks do. NULL did proper casing and upper casing in all the right spots and did some block indenting on the BETWEEN... most people just throw some lower case crap together on a single line.

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

  • December 31, 2008 at 9:38 am

    #919724

    In case someone's reading this and doesn't have the U.S. English default set on their instance, don't forget to check @@datefirst to make sure which day starts your week.

    You'll just need to adjust the number of the day for Friday in the code above from 5 (U.S. English default number for Friday) to whatever it is on your box.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • January 1, 2009 at 2:26 pm

    #920033

    Datefirst doesn't even come into play on this...

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

  • January 1, 2009 at 3:56 pm

    #920042

    It does unfortunately..

    set language us_english

    go

    select @@datefirst, datepart(dw, getdate())

    go

    set language british

    go

    select @@datefirst, datepart(dw, getdate())

    I had to cope with this on a few occasions.

    Piotr

    ...and your only reply is slàinte mhath

  • January 1, 2009 at 5:59 pm

    #920053

    Piotr Rodak (1/1/2009)

    It does unfortunately..

    set language us_english

    go

    select @@datefirst, datepart(dw, getdate())

    go

    set language british

    go

    select @@datefirst, datepart(dw, getdate())

    I had to cope with this on a few occasions.

    Piotr

    That would be true if I used DATEPART(DW,somedate). I have not used DATEPART(DW) anywhere in the code I wrote and, as a result, @@Datefirst has zero impact on any of my code. 😉 In fact, none of the code used to solve the problem in this entire thread uses DATEPART at all and DATEDIFF is NOT affected by DateFirst settings.

    SET DATEFIRST 1

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 2

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 3

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 4

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 5

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 6

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    SET DATEFIRST 7

    SELECT DATEDIFF(wk,0,'01/06/1900'), DATEDIFF(wk,0,'01/07/1900')

    Like I said... Datefirst has no bearing on this problem. 😀

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

  • January 1, 2009 at 6:59 pm

    #920060

    Now, that's a cunning trick I see now Jeff :w00t:

    Not earlier than yesterday I said to myself 'one day I have to get those dateadd-datediff calculations sorted out'.

    And now I have to go back to my code and think if I can make it (even) better :Whistling:

    🙂

    ...and your only reply is slàinte mhath

  • January 1, 2009 at 7:37 pm

    #920069

    Piotr Rodak (1/1/2009)

    Now, that's a cunning trick I see now Jeff :w00t:

    Not earlier than yesterday I said to myself 'one day I have to get those dateadd-datediff calculations sorted out'.

    And now I have to go back to my code and think if I can make it (even) better :Whistling:

    🙂

    Cunning is right... and the poster known as "NULL" knew it as well...

    Day "0" is 01/01/1900... and it was a Monday... Guess what happens if you add 4 days to any Monday? You get a Friday... every time.

    Thanks for the feedback, Piotr.

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

  • January 5, 2009 at 6:23 am

    #921068

    Jeff Moden (1/1/2009)

    Piotr Rodak (1/1/2009)

    Now, that's a cunning trick I see now Jeff :w00t:

    Not earlier than yesterday I said to myself 'one day I have to get those dateadd-datediff calculations sorted out'.

    And now I have to go back to my code and think if I can make it (even) better :Whistling:

    🙂

    Cunning is right... and the poster known as "NULL" knew it as well...

    Day "0" is 01/01/1900... and it was a Monday... Guess what happens if you add 4 days to any Monday? You get a Friday... every time.

    Thanks for the feedback, Piotr.

    Nice, that makes me feel better. I was wondering as I was posting how I was noticing something that Jeff missed. Luckily, I can always count on him setting me straight. 😀

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • January 5, 2009 at 8:06 pm

    #921546

    jcrawf02 (1/5/2009)

    Nice, that makes me feel better. I was wondering as I was posting how I was noticing something that Jeff missed. Luckily, I can always count on him setting me straight. 😀

    Heh... I gotta give it up someday... or a least slow down and get some sleep. 😛 Anyway, thanks for the good natured feedback.

    --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 15 (of 16 total)

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