set default start day as monday in sql server

Question

I want to find the date of Monday of current week

declare @td datetime
set @td = CONVERT(datetime,'28-07-2013',105)
print @td

declare @dt datetime
set @dt = (SELECT DATEADD(wk, DATEDIFF(wk,0,@td), 0) MondayOfCurrentWeek)
print @dt

This is giving me correct result , only if i have giv the sunday date , its giving me wrong answer for example in above 28 is sunday of july week, so as per my need i should get 22 07 2013 but as by default sql server take Sunday as first day of week it giving me 29-07-2013

I have also tried

Set DateFirst 1

went through

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cc3493a-7ae5-4759-ab2a-e7683165320b/problem-with-datediff-and-datefirst

dint help me,

i hope to get help here from the experts

Thank you

possible duplicate of [Is it possible to set start of week for T-SQL DATEDIFF function?](http://stackoverflow.com/questions/1101892/is-it-possible-to-set-start-of-week-for-t-sql-datediff-function) — Alexander, Jul 01 '13 at 10:38
I have given the full syntax in question , even you can copy and paste and try, i am using sql server 2008 R2 — Md. Parvez Alam, Jul 01 '13 at 10:45

score 2 · Accepted Answer · answered Jul 01 '13 at 10:53

2

I would, instead, use the following construct:

set @dt = DATEADD(day,
    -(DATEPART(weekday,@td) - DATEPART(weekday,'20130729') + 7) % 7
    ,@td)

This should always work out the correct offset to subtract from a particular date to get to the previous monday - and it doesn't depend on what DATEFIRST settings are in effect. All that it does depend on is that 29th July 2013 was a Monday. You never change that fixed date.

answered Jul 01 '13 at 10:53

Damien_The_Unbeliever

234,701
27
340
448

@Md.ParvezAlam - for what input? If I take e.g. your example, I get "22nd July 2013" – Damien_The_Unbeliever Jul 01 '13 at 11:18
2013-07-01 00:00:00.000 This is output of your answer for set @td = CONVERT(datetime,'28-07-2013',105) – Md. Parvez Alam Jul 01 '13 at 11:27
secondly i dint understand what you have taken '20130729' as hardcoded – Md. Parvez Alam Jul 01 '13 at 11:29
@Md.ParvezAlam - If you take my answer, and change none of it, and use it to replace your current line that starts `set @dt = ...`, it should work. As I tried to say below the answer, the only important thing about `20130729` is that it's a Monday. We're using it to fix things so that we don't have to know what the current `DATEFIRST` setting is. – Damien_The_Unbeliever Jul 01 '13 at 11:34
Hey sorrry @damien by mistake i had mixed with other output, sorry and Thanks its working, thanks alot – Md. Parvez Alam Jul 01 '13 at 12:00

score 1 · Answer 2 · answered Jul 01 '13 at 11:59

I prefer using a calendar table for queries like this. On my local system . . .

select max(cal_date)
from calendar
where cal_date <= '2013-07-28'
  and day_of_week = 'Mon'

The greatest benefit of calendar tables, in my opinion, is that queries can be seen to be obviously correct.

score 1 · Answer 3 · answered Jul 05 '13 at 12:57

1

you can find here full details about first date of week and last date of week http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50624

answered Jul 05 '13 at 12:57

Your father

97
1
11

score 1 · Answer 4 · answered Jul 17 '13 at 09:25

Try it....solve your problem

DECLARE @DayOfTheWeek NVARCHAR(12)
SET @DayOfTheWeek=(SELECT DATENAME(DW,GETDATE()))
IF(@DayOfTheWeek='SUNDAY')
    BEGIN
        SELECT DATEADD(WK, DATEDIFF(WK,0,DATEADD(DAY, -1, GETDATE())), 0) MONDAYOFCURRENTWEEK
    END
ELSE
    BEGIN
        SELECT DATEADD(WK, DATEDIFF(WK,0,GETDATE()), 0) MONDAYOFCURRENTWEEK
    END

set default start day as monday in sql server

4 Answers4