Reputation: 232
I need to get the date of monday and sunday this week.
I got this atm, but it dosn't give the right result when i set the current date to the same monday.
DECLARE @date datetime
set @date = '2013-09-01 11:15:51'
SELECT DATEADD(day,
-1 - (DATEPART(dw, CONVERT (date, @date)) + @@DATEFIRST -2) % 7,
CONVERT (date, @date)
) AS ThisModay,
DATEADD(day, 6, CONVERT (date, @date)) as NextSunday
when trying that i get 2013-08-25 as monday, but it should be 2013-09-01
How can i do this ? , have current week as results at all time. (current date should always be same as or between the two results)
Upvotes: 4
Views: 7454
Reputation: 32453
My version:
DECLARE @date datetime = '2013-08-31 11:15:51'
DECLARE @Monday INT = 2
DECLARE @NextSunday INT = 8
SELECT
DATEADD(day, (@Monday - DATEPART(dw, @date)), @date) AS ThisMonday
, DATEADD(day, (@NextSunday - DATEPART(dw, @date)), @date) AS NextSunday
Here SQL Fiddle for testing
Upvotes: 0
Reputation: 2861
try following
Actually you dont need SET DATEFIRST but this is for precaution only
SET DATEFIRST 7;
DECLARE @date datetime
set @date = '2013-09-11 11:15:51'
SELECT cast(DATEADD(wk, DATEDIFF(wk, 0, @date), 0) as DATE) AS ThisModay,
DATEADD(day, 6, CONVERT (date, @date)) as NextSunday
Upvotes: 0
Reputation: 89
Try
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),7)
for sunday
SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),0)
for monday
since by default first day of week is monday starting from 0
Upvotes: 2
Reputation: 1002
my previous answer was wrong, heres the correction
DECLARE @date datetime
declare @wd int
set @date = '2013-09-06 11:15:51'
set @wd = case when datepart(weekday,@date)<2 then 7-datepart(weekday,@date) else datepart(weekday,@date)-2 end
select DATEADD(day, -@wd,@date) As Monday,
DATEADD(day, 6-@wd,@date) As Sunday
Upvotes: 1
Reputation: 26
Before your declare code:
set @@DATEFIRST = 1
by default it's 7 , meaning the first of the week is Sunday . When you put it equals to 1 , the first day will be monday,
Upvotes: 0