Blog on software engineering
Get first day of current week or last week with TSQL

Get first day of current week or last week with TSQL

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.