Home Forums SQL Server 2005 T-SQL (SS2K5) How to get week of a mont with input parameter date. RE: How to get week of a mont with input parameter date.

  • Lynn Pettis (9/12/2012)


    Lynn Pettis (9/11/2012)


    Here is a bit of code that returns what you are looking for:

    declare @TestDate date = '2012-09-11';

    with SevenRows(n) as (select row_number() over (order by (select null)) - 1 from (values (1),(1),(1),(1),(1),(1),(1))dt(n))

    select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;

    Code rewritten for SQL Server 2005:

    declare @TestDate date = '2012-09-11';

    with SevenRows(n) as (select

    row_number() over (order by (select null)) - 1

    from (select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1)dt(n))

    select dateadd(dd, n,dateadd(wk,datediff(wk,0,dateadd(dd,-1,@TestDate)),0)) from SevenRows;

    I have a general distrust for the "wk" datepart even though it doesn't seem to matter matter here. With that thought in mind, here's a bit of code that uses a zero based Tally Table. It can be easily modified to handle a unit based Tally Table if needed. If nothing else, it makes for some really simple code.

    SELECT DATEADD(dd,DATEDIFF(dd,-1,GETDATE())/7*7+(t.N),-1)

    FROM dbo.Tally t

    WHERE t.N BETWEEN 0 AND 6

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