I am using the following queries to

[LuvASP]

I am using the following queries to get the WeekStarting date and WeekEnding date depending on today's date. For e.g. the weekstarting date for today would be 6/18(Monday) and weekending would be 6/24(Sunday).

(Select dateadd(d, -(datepart(dw,getdate())-2), getdate())) as weekstarting
(Select dateadd(d, -(datepart(dw,getdate())-2) + 6, getdate())) as weekending

It works well for all dates from Monday through Saturday but if I run this on Sunday I get the next weeks starting and ending date.
Is there a solution to this. I cannot use @@datefirst function of SQL Server nor can I use user-defined functions as I am using SQL Server 7.0

Thanks for any help.

 

[tlbroadbent]

Try this... using the CASE statement...

Select dateadd(d, case datepart(dw,getdate()) When 1 Then -6 Else -(datepart(dw,getdate())-2) End , getdate()) as weekstarting
Select dateadd(d, case datepart(dw,getdate()) When 1 Then 0 Else -(datepart(dw,getdate())-2) + 6 End, getdate()) as weekending Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[LuvASP]

Thank you very much. It worked.