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.

  • September 12, 2012 at 7:39 am

    #1536709

    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;