Post reply

Date Range of Week

  • January 12, 2009 at 2:21 pm

    #73177

    I have a query which returns data based on the week number using the datepart function. However, instead of displaying the data as week 1, week 2, week 3, etc., I would like to display the actual dates of that week, ie. Jan 4 - 10, Jan 11 - 17 etc. Is there a way to retrieve the date range of a week?

  • January 12, 2009 at 4:18 pm

    #925624

    SELECT MIN(Date), -- use some formatting here if you need it

    MAX((Date), -- again, format the string to the requirements

    WeekNo

    FROM dbo.Calendar

    GROUP BY WeekNo

    _____________
    Code for TallyGenerator

  • January 13, 2009 at 2:37 am

    #925720

    First OP have to define what a week means to him/her.

    N 56°04'39.16"
    E 12°55'05.25"

  • January 13, 2009 at 6:59 am

    #925832

    I'm getting an incorrect syntax error when I do From dbo.Calendar. I am using MS Sql 2000 and I don't see this table.

    Just to clarify my original post. I am grouping records by week using the DatePart function. I am just using Sql's standard week, Sunday thru Saturday I believe. On my report, instead of displaying Week 1, Week 2, etc. I want to show the date range of each week.

  • January 13, 2009 at 7:52 am

    #925892

    Try this

    CREATE TABLE #T (DT DATE)

    INSERT INTO #T(DT)

    SELECT '2009-01-01' DT

    UNION SELECT '2009-01-10'

    UNION SELECT '2009-01-12'

    UNION SELECT '2009-01-08'

    UNION SELECT '2009-01-22'

    UNION SELECT '2009-01-16'

    UNION SELECT '2009-01-14'

    UNION SELECT '2009-01-26'

    UNION SELECT '2009-01-06'

    SELECT DT, DATEPART (WK, DT) WEEKNUMBER,

    CAST(DAY(DATEADD(D, 1-DATEPART (DW, DT), DT)) AS VARCHAR) + '-' + CAST(DAY(DATEADD(D, 7-DATEPART (DW, DT), DT)) AS VARCHAR) as WEEK

    FROM #T

    DROP TABLE #T

    Regards,
    Nitin

  • January 13, 2009 at 8:26 am

    #925924

    bdewulf (1/13/2009)

    I'm getting an incorrect syntax error when I do From dbo.Calendar. I am using MS Sql 2000 and I don't see this table.

    Just to clarify my original post. I am grouping records by week using the DatePart function. I am just using Sql's standard week, Sunday thru Saturday I believe. On my report, instead of displaying Week 1, Week 2, etc. I want to show the date range of each week.

    There is no such thing as a standard week.

    My SQL Server starts monday and ends sunday.

    Now we have established week is sunday -> monday.

    Now to the question how to deal with New Year, Dec 31-Jan 1.

    What defines first week of year?

    N 56°04'39.16"
    E 12°55'05.25"

  • January 13, 2009 at 8:27 am

    #925927

    That's what I needed. Thanks so much for your help.

  • January 13, 2009 at 8:36 am

    #925934

    I suggest building a calendar table, or at least a calendar view, to store such data. Lots of advantages to that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 13, 2009 at 8:39 am

    #925942

    Is week number really OK?

    I guess I am spoiled and used to ISO standard for week numbering.

    CREATE TABLE #T (DT DATETIME)

    INSERT INTO #T(DT)

    SELECT '2008-12-30'

    UNION SELECT '2008-12-31'

    UNION SELECT '2008-12-29'

    UNION SELECT '2008-12-28'

    UNION SELECT '2008-12-27'

    UNION SELECT '2009-01-01'

    UNION SELECT '2009-01-02'

    UNION SELECT '2009-01-03'

    UNION SELECT '2009-01-04'

    UNION SELECT '2009-01-05'

    UNION SELECT '2009-01-06'

    UNION SELECT '2009-01-07'

    UNION SELECT '2009-01-08'

    UNION SELECT '2009-01-09'

    UNION SELECT '2009-01-10'

    UNION SELECT '2009-01-12'

    UNION SELECT '2009-01-08'

    UNION SELECT '2009-01-22'

    UNION SELECT '2009-01-16'

    UNION SELECT '2009-01-14'

    UNION SELECT '2009-01-26'

    UNION SELECT '2009-01-06'

    SELECT DT, DATEPART (WK, DT) WEEKNUMBER,

    CAST(DAY(DATEADD(D, 1-DATEPART (DW, DT), DT)) AS VARCHAR) + '-' + CAST(DAY(DATEADD(D, 7-DATEPART (DW, DT), DT)) AS VARCHAR) as WEEK,

    replace(convert(char(6), dt, 0), ' ', ' ') + ' -- ' + replace(convert(char(6), dt + 6, 0), ' ', ' ') AS peso

    FROM #T

    DROP TABLE #T

    N 56°04'39.16"
    E 12°55'05.25"

  • January 13, 2009 at 8:39 am

    #925943

    bdewulf (1/13/2009)

    I'm getting an incorrect syntax error when I do From dbo.Calendar. I am using MS Sql 2000 and I don't see this table.

    Just to clarify my original post. I am grouping records by week using the DatePart function. I am just using Sql's standard week, Sunday thru Saturday I believe. On my report, instead of displaying Week 1, Week 2, etc. I want to show the date range of each week.

    What folks are suggesting is that you take the time to build a thing called a "Calendar Table". That way, you don't have to keep reinventing the wheel on nor use a bunch of functions on things like 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 13, 2009 at 8:45 am

    #925955

    Use this function to create the calendar table

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

    It has tons of date limits to try out.

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 11 posts - 1 through 10 (of 10 total)

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