Michael Valentine Jones (12/4/2012)

---

Lynn Pettis (12/4/2012)

---

Michael Valentine Jones (12/4/2012)

---

select

a.*,

Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

from

( -- Test Data

select [Date]= getdate()-2union all

select [Date]= getdate()-1union all

select [Date]= getdate()union all

select [Date]= getdate()+1union all

select [Date]= getdate()+2union all

select [Date]= getdate()+3union all

select [Date]= getdate()+4union all

select [Date]= getdate()+5union all

select [Date]= getdate()+6union all

select [Date]= getdate()+7union all

select [Date]= getdate()+8

) a

order by

a.[Date]

Results:

Date Monday

----------------------- -----------------------

2012-12-02 13:05:22.770 2012-11-26 00:00:00.000

2012-12-03 13:05:22.803 2012-12-03 00:00:00.000

2012-12-04 13:05:22.803 2012-12-03 00:00:00.000

2012-12-05 13:05:22.803 2012-12-03 00:00:00.000

2012-12-06 13:05:22.803 2012-12-03 00:00:00.000

2012-12-07 13:05:22.803 2012-12-03 00:00:00.000

2012-12-08 13:05:22.803 2012-12-03 00:00:00.000

2012-12-09 13:05:22.803 2012-12-03 00:00:00.000

2012-12-10 13:05:22.803 2012-12-10 00:00:00.000

2012-12-11 13:05:22.803 2012-12-10 00:00:00.000

2012-12-12 13:05:22.803 2012-12-10 00:00:00.000

Or:

select

a.*,

Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

from

( -- Test Data

select [Date]= getdate()-2union all

select [Date]= getdate()-1union all

select [Date]= getdate()union all

select [Date]= getdate()+1union all

select [Date]= getdate()+2union all

select [Date]= getdate()+3union all

select [Date]= getdate()+4union all

select [Date]= getdate()+5union all

select [Date]= getdate()+6union all

select [Date]= getdate()+7union all

select [Date]= getdate()+8

) a

order by

a.[Date]

Not completely the same, though:

select

a.*,

Monday = dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

from

( select [Date]= convert(datetime,'17530101') ) a

select

a.*,

Monday = dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,a.Date)), 0)

from

( select [Date]= convert(datetime,'17530101') )

Results:

Date Monday

----------------------- -----------------------

1753-01-01 00:00:00.000 1753-01-01 00:00:00.000

(1 row(s) affected)

Date Monday

----------------------- -----------------------

Msg 517, Level 16, State 1, Line 7

Adding a value to a 'datetime' column caused an overflow.