Finding last Monday in SQL

[RyanEK]

Hi All,

Would appreciate the help. What's the best way to return the date of last monday?

I could use the following:

select DATEADD(wk, DATEDIFF(wk,0,getdate()), -7)

... but this assumes that the first day of the week is Sunday. I need it so that the first day of the week is Monday.

Can anyone help? Thanks

Ryan

 

[RyanEK]

Oops... my example should actually be:

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)-1

 

[pabowen]

Ryan, are you just wanting a statement that provides the date of the last monday. Meaning if today is Wednesday, May 10th, 2006. You want the date for Monday, May 8th?

Please let me know, and I can churn something up.

Patrick

 

[RyanEK]

Patrick,

Thanks heaps mate, but I finally came up with a solution. I actually wanted it so that if today is May 10, the answer would be Monday May 1.

I used the following:

select DateAdd(d, -((@@DATEFIRST + DatePart(dw, getdate()) -2) % 7)-7, getdate())

But hey, if you know of a better solution, i'm all ears!

Thanks again

Ryan