CREATE FUNCTION dbo.ReturnDates
RETURNS @Dates Table
(StartDate datetime,
EndDate datetime
)
AS
BEGIN
DECLARE @PrevSat datetime,@StartDate datetime,@EndDate datetime

SET @PrevSat= DATEADD(dd,(-1) * DATEPART(dw,GETDATE()),GETDATE())
SET @StartDate= CASE WHEN DATEPART(dw,DATEADD(dd,-41,@PrevSat)) =7
THEN DATEADD(dd,-41,@PrevSat) ELSE DATEADD(dd,(-1) * DATEPART(dw,DATEADD(dd,-41,@PrevSat)),DATEADD(dd,-41,@PrevSat))

SET @EndDate = DATEADD(dd,6,@StartDate)

INSERT INTO @Dates
SELECT @StartDate,@EndDate

RETURN
END

quote:

---

Originally posted by Trudye

Visakh16, thanks for responding so quickly.
It looks great but I’m not sure what it’s doing. What is the * in the DateAdd func for? What happens if the
-41 days lands you on a Wednesday. How do you loop backwards until you get to the previous Saturday to get the start date? The -41 is not absolute it has to be at least 41 days (i.e. the first Saturday prior to the -41st day). I’m so sorry for the confusion. Or maybe I’m waaaay off base and that’s what you code is doing.

OBTW I just found out I can use a For/While statement. Would that get me what I want?

---

quote:

---

Originally posted by Trudye

Thanks Guys for your responses. Both responses are very slick and I would never have been able to come up with them on my own. I will add them to my library.

viaskh16, I noticed you used a UDF, is there a reason for that? I wrote a stored proc to calc the date. My only problem is I have never called a stored proc from within a stored proc, not sure how it works. I guess I rewrite it and make it a UDF.

But before I did that I thought I would ask what the advantages/disadvantages are? Why you prefer a UDF over a stored proc?

I need to call my DateProc from my Import_Export_Proc, have it return the @start_date and @end_date. Both dates are calc'd in the DateProc, nothing need to be passed into the DateProc. Any ideas how I do that?

Thanks again Guys,
Trudye

---

quote:

---

Originally posted by Trudye

Thanks Guys for your responses. Both responses are very slick and I would never have been able to come up with them on my own. I will add them to my library.

viaskh16, I noticed you used a UDF, is there a reason for that? I wrote a stored proc to calc the date. My only problem is I have never called a stored proc from within a stored proc, not sure how it works. I guess I rewrite it and make it a UDF.

But before I did that I thought I would ask what the advantages/disadvantages are? Why you prefer a UDF over a stored proc?

I need to call my DateProc from my Import_Export_Proc, have it return the @start_date and @end_date. Both dates are calc'd in the DateProc, nothing need to be passed into the DateProc. Any ideas how I do that?

Thanks again Guys,
Trudye

---

quote:

---

Originally posted by Trudye

visakh16, did I miss something. I entered this:
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate=StartDate,@EndDate=EndDate FROM dbo.ReturnDates()
Print 'Returned Values'
Print @StartDate
Print @EndDate

Don't I need to define the parms in dbo.ReturnDates, like you do when you call a func in VB/VBA? Either that or make them global values?

---