CODEWITHSUNDEEP
sql-server
Niclas Schumacher
Niclas Schumacher

Reputation: 232

Get date of monday and sunday this week

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

Answers (5)

Fabio
Fabio

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

Dhaval
Dhaval

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

Gemini
Gemini

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

Rob White
Rob White

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

Dancharim
Dancharim

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

Related Questions