Professional Web Applications Themes

Begin & End of Week calc ??? Help!! - Microsoft SQL / MS SQL Server

I wonder: Did any of the repliers check their work? How about checking this revised version? And for some surprises compare to the varations previously posted. create table sometable (datecol datetime) insert into sometable values ('7/25/2003') insert into sometable values ('7/26/2003') insert into sometable values ('7/27/2003') insert into sometable values ('7/28/2003') insert into sometable values ('7/29/2003') insert into sometable values ('7/30/2003') insert into sometable values ('7/31/2003') insert into sometable values ('8/1/2003') insert into sometable values ('8/2/2003') insert into sometable values ('8/3/2003') insert into sometable values ('8/4/2003') -- SET DATEFIRST 7 -- 1 2 3 4 5 6 7 SELECT DATEFIRST ...

  1. #1

    Default Re: Begin & End of Week calc ??? Help!!

    I wonder:
    Did any of the repliers check their work?

    How about checking this revised version?
    And for some surprises compare
    to the varations previously posted.

    create table sometable (datecol datetime)
    insert into sometable values ('7/25/2003')
    insert into sometable values ('7/26/2003')
    insert into sometable values ('7/27/2003')
    insert into sometable values ('7/28/2003')
    insert into sometable values ('7/29/2003')
    insert into sometable values ('7/30/2003')
    insert into sometable values ('7/31/2003')
    insert into sometable values ('8/1/2003')
    insert into sometable values ('8/2/2003')
    insert into sometable values ('8/3/2003')
    insert into sometable values ('8/4/2003')

    -- SET DATEFIRST 7 -- 1 2 3 4 5 6 7
    SELECT DATEFIRST

    SELECT datecol,
    DATEADD(
    DAY,0-(DATEPART(DW,datecol)-1)%7,datecol
    ) AS week_beginning,
    DATEADD(
    DAY,6-(DATEPART(DW,datecol)-1)%7,datecol
    ) AS week_ending
    FROM Sometable
    ORDER BY datecol

    Bye,
    Delbert Glass


    Delbert Guest

  2. #2

    Default Re: Begin & End of Week calc ??? Help!!

    Sounds like that would be ok.
    But realize something went wrong.
    Take a look at the following.

    create table sometable (datecol datetime)
    insert into sometable values ('7/25/2003')
    insert into sometable values ('7/26/2003')
    insert into sometable values ('7/27/2003')
    insert into sometable values ('7/28/2003')
    insert into sometable values ('7/29/2003')
    insert into sometable values ('7/30/2003')
    insert into sometable values ('7/31/2003')
    insert into sometable values ('8/1/2003')
    insert into sometable values ('8/2/2003')
    insert into sometable values ('8/3/2003')
    insert into sometable values ('8/4/2003')

    declare anyBegin datetime
    set anyBegin = '20030628'

    select
    datecol,
    dateadd(week,datediff(week,anyBegin,datecol),any Begin) BeginWeek,
    dateadd(week,datediff(week,anyBegin,datecol),any Begin) + 6 BeginWeek
    from sometable
    order by datecol

    I get these results.

    date col begin week begin week (end week)
    2003-07-25 00:00:00.000 2003-07-26 00:00:00.000 2003-08-01 00:00:00.000
    2003-07-26 00:00:00.000 2003-07-26 00:00:00.000 2003-08-01 00:00:00.000
    2003-07-27 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-07-28 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-07-29 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-07-30 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-07-31 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-08-01 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-08-02 00:00:00.000 2003-08-02 00:00:00.000 2003-08-08 00:00:00.000
    2003-08-03 00:00:00.000 2003-08-09 00:00:00.000 2003-08-15 00:00:00.000
    2003-08-04 00:00:00.000 2003-08-09 00:00:00.000 2003-08-15 00:00:00.000

    Which assigns
    Sundays, Mondays, Tuesdays, Wednesdays, Thursdays, and Fridays
    to the week after the week they should be assigned too.
    Which obviously is not right
    since the assigned week does not include the given date :-(
    Oops.

    Bye,
    Delbert Glass

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... 
    >[/ref]


    Delbert Guest

  3. #3

    Default Re: Begin & End of Week calc ??? Help!!

    Oops. Silly me. Maybe this will work better...


    declare anyBegin datetime
    set anyBegin = '20030628'

    select
    datecol,
    dateadd(day,datediff(day,anyBegin,datecol)/7*7,anyBegin) BeginWeek,
    dateadd(day,datediff(day,anyBegin,datecol)/7*7,anyBegin) + 6 BeginWeek
    from sometable
    order by datecol
    go

    SK

    Delbert Glass wrote:
     [/ref]
    >
    >
    >
    >[/ref]

    Steve Guest

  4. #4

    Default Re: Begin & End of Week calc ??? Help!!

    Oops, again.

    The new formulation,
    does not handle dates prior to the magic date properly.
    Some data to see with is included below.

    In addition to having days whose date
    does not fall within the date range of their assigned week,
    we have a week with more then seven days
    assigned to it :-(

    insert into sometable values ('06/19/2003')
    insert into sometable values ('06/20/2003')
    insert into sometable values ('06/21/2003')
    insert into sometable values ('06/22/2003')
    insert into sometable values ('06/23/2003')
    insert into sometable values ('06/24/2003')
    insert into sometable values ('06/25/2003')
    insert into sometable values ('06/26/2003')
    insert into sometable values ('06/27/2003')
    insert into sometable values ('06/28/2003')
    insert into sometable values ('06/29/2003')
    insert into sometable values ('06/30/2003')
    insert into sometable values ('07/01/2003')
    insert into sometable values ('07/02/2003')
    insert into sometable values ('07/03/2003')
    insert into sometable values ('07/04/2003')
    insert into sometable values ('07/05/2003')

    Bye,
    Delbert Glass

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    Delbert Guest

Similar Threads

  1. Replies: 3
    Last Post: December 22nd, 10:42 PM
  2. calc date in week + day
    By angelo in forum FileMaker
    Replies: 13
    Last Post: December 4th, 03:45 AM
  3. Calc help please
    By The in forum FileMaker
    Replies: 8
    Last Post: November 6th, 06:37 AM
  4. How do I add another test to this calc?
    By The Wolf in forum FileMaker
    Replies: 6
    Last Post: October 12th, 01:44 AM
  5. Date Due Calc
    By The Wolf in forum FileMaker
    Replies: 19
    Last Post: October 7th, 06:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139