To get the first day of the current week use:

select dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate())

To get the first day of previous week and last day of previous week use:

select dateadd(day, -8, dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate()))
select dateadd(day, -1, dateadd(dd, (datepart(dw, getdate()) * -1) + 2, getdate()))

You can also, use a variable for the current date and time, like:

declare @currentDateTime as datetime
set @currentDateTime = '2011-01-15 07:47:31.887' 

select dateadd(day, -8, dateadd(dd, (datepart(dw, @currentDateTime) * -1) + 2, @currentDateTime))
-- Result: 2011-01-02 07:47:31.887

select dateadd(day, -1, dateadd(dd, (datepart(dw, @currentDateTime) * -1) + 2, @currentDateTime))
-- Result: 2011-01-09 07:47:31.887

One Comment

  1. My @@DATEFIRST = 7

    When I run the following query,
    select dateadd(dd, (datepart(dw, ‘2015-01-04’) * -1) + 2, ‘2015-01-04’)

    I get the following incorrect result:
    2015-01-05 00:00:00.000

    jower fasdwoerhasdf

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.