14 December, 2009
0 Comments
1 category
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
Tags: T-SQL
Category: Uncategorized
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