DateAdd/DateDiff formula for Last full business week?

[starbase59]

Currently I passing start date and end date parameters in a stored procedure.

Rather than having to manually enter the parameters, I want to incorporate a sql formula using datediff and/or dateadd, such that it will take today's date and return a period of the last full business week.

In other words,for example, if today were 10/28/03, the start date would return 10/20/03 and the end date would return 10/24/03.

This way the report runs more automatically.

Thank you!

 

[nigelrivett]

Here's a start

declare @dte datetime
select @dte = '20031028'

select datepart(dw,@dte)

declare @start datetime, @end datetime
select @Start = dateadd(dd,-1*datepart(dw,@dte)-5,@dte)
select @end = @start + 4
select @start, @end

You will have to deal with what happens when it gets close to the end of the week. It will also depend on the setup of the server with regard to the start day of the week. If you wish to make it independent use @@datefirst.

to test it run for a range of days

set nocount on
declare @dte datetime
select @dte = '20031020'

while @dte < '20031201'
begin
select @dte = dateadd)dd,1,@dte)
select datepart(dw,@dte)
declare @start datetime, @end datetime
select @Start = dateadd(dd,-1*datepart(dw,@dte)-5,@dte)
select @end = @start + 4
select @dte, @start, @end
end





======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.