locked
last week and last 2 week date range RRS feed

  • Question

  • Hi All,

    i need to query out last week first day date time (sunday) 2011-04-03 00:00:00.000 and last week last day (satarday) 2011-04-09 23:59:59.997

    and

    2 weeks ago dates  2011-03-27 00:00:00.000  to 2011-04-09 23:59:59.997

    how can we get above dates..please help..thnx

    Tuesday, April 12, 2011 7:07 AM

Answers

  • Hi,

    try this


    declare @d int
    set @d=(select datepart(dw,'2011-04-15')%7)
    select DATEADD(day,-@d,'2011-04-15')
    select DATEADD(WEEK,-2,DATEADD(day,-@d,'2011-04-15'))+1


    Thanks and regards, Rishabh
    • Marked as answer by Yazdani ISTS Tuesday, April 12, 2011 9:07 AM
    Tuesday, April 12, 2011 7:29 AM

All replies

  • Hi, the below tsql will solve your problem -

    SELECT 
     DATEADD(DD, 1 - DATEPART(DW, DATEADD(DD,-7,GETDATE())),DATEADD(DD,-7,GETDATE())) FirstDayOfPreviousWeek,
     DATEADD(DD,-7,GETDATE()) PreviousWeekDateTime,
     DATEADD(DD,6,DATEADD(DD, 1 - DATEPART(DW, DATEADD(DD,-7,GETDATE())),DATEADD(DD,-7,GETDATE()))) LastDayOfPreviousWeek,
     DATEADD(DD, 1 - DATEPART(DW, GETDATE()),GETDATE()) FirstDayOfCurrentWeek,
     GETDATE() CurrentDateTime,
     DATEADD(DD,6,DATEADD(DD, 1 - DATEPART(DW, GETDATE()),GETDATE())) LastDayOfCurrentWeek,
     DATEADD(DD, 1 - DATEPART(DW, DATEADD(DD,7,GETDATE())),DATEADD(DD,7,GETDATE())) FirstDayOfNextWeek,
     DATEADD(DD,7,GETDATE()) NextWeekDateTime,
     DATEADD(DD,6,DATEADD(DD, 1 - DATEPART(DW, DATEADD(DD,7,GETDATE())),DATEADD(DD,7,GETDATE()))) LastDayOfNextWeek
    


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Proposed as answer by vinaypugalia Tuesday, April 12, 2011 7:24 AM
    Tuesday, April 12, 2011 7:24 AM
  • Hi,

    try this


    declare @d int
    set @d=(select datepart(dw,'2011-04-15')%7)
    select DATEADD(day,-@d,'2011-04-15')
    select DATEADD(WEEK,-2,DATEADD(day,-@d,'2011-04-15'))+1


    Thanks and regards, Rishabh
    • Marked as answer by Yazdani ISTS Tuesday, April 12, 2011 9:07 AM
    Tuesday, April 12, 2011 7:29 AM
  • thnx Rishabh
    Tuesday, April 12, 2011 9:08 AM