how to pass previous week dates in stored proc parameters.
-
i am planning to create package with an execute sql task where it calls procedure with parameters (@fromdate, @todate)
@FromDate DATETIME
@ToDate DATETIME
For example if i scheudle job to run this package every monday example (May9). the execute task should run for previous week dates May 2 to May8(Monday to Sunday)
EXEC sp_getdetails '05-02-2022', '05-08-2022'
How to build expressions for the variables and pass the variables to this procedure?
-
A calendar table is great for that.
But if you want to derive it dynamically, and you're always executing on the day following the end of the date range:
DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;
SELECT @FromDate = CAST(DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) AS DATETIME),
@ToDate = CAST(CAST(GETDATE() AS DATE) AS DATETIME);-- If @ToDate is exclusive (up to @ToDate)
SELECT @FromDate, @ToDate; -
You can create a couple variables and set them with the expression parameter they will evaluate at run time.
Then have an Exec SQL Task preferably setup with an ADO.Net connection type.
Then you select stored procedure as the command type and on the parms tab you enter the exact names of the parms as they exist in your procedure and supply the variables from the dropdown.
That's one option. It's pretty straight forward.
-
What would be interesting is to see if the stored procedure actually uses the end date correctly or if you going to miss all but the firs 3.3 milliseconds of that end date.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - ratbak wrote:
A calendar table is great for that. But if you want to derive it dynamically, and you're always executing on the day following the end of the date range:
DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;
SELECT @FromDate = CAST(DATEADD(DAY,-7,CAST(GETDATE() AS DATE)) AS DATETIME),
@ToDate = CAST(CAST(GETDATE() AS DATE) AS DATETIME);-- If @ToDate is exclusive (up to @ToDate)
SELECT @FromDate, @ToDate;The trouble with that is, what happens if the server is down on Monday for some reason? People are still going to want their reports and, if they run that on, say, a Tuesday, the correct date won't be used for either the start or the end date.
The OP also provided example dates that run from Monday to the following Sunday and that's why I posted that it would be interesting to see what the proc actually does for criteria using the two parameters.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The "base date" for SQL Server for temporal datatypes is 01 Jan 1900 and it has a "Date Serial#" of "0". All other dates are based on the number of days from that "0" day. If a date begins after that date, it will have a positive number. If it occurs before that date, it will have a negative number. And it's also true that date is a MONDAY.
You must NOT use the WEEK/WK part for this type of calculation because, in order to keep DATEDIFF as a determinate function, they had to build it so the first day of the first week of any given year is the first day of the year. Only 1 out of 7 years (on average) start on a Monday. So, don't use the WEEK/WK datepart in DATEDIFF unless you really want what it offers...
.... and we don't want what it offers for finding the start and end dates of a previous week starting on a Monday and ending on a Sunday.
We DO know that 01 Jan 1900 was a Monday. If we count the number of days since that date and divide by an integer 7, we'll know the number of WHOLE weeks that have passed for the current date. If we multiply that by an integer 7, we'll know the date serial number for the previous Monday (even if today is the previous Moinday). Subtract 7 from that and we have the Monday Starting Date of the previous week. We can use the same formula and subtract 1 from the result to get the current most recent Monday and subtract 1 from that to get the Sunday that last week ended on.
There are a whole lot of different tricks can use to remove a math step but we'll keep it simple for this problem. So, the code would look like this...
--===== Create and populate the parameters for last week (Mon to Sun)
-- This is the part that would go into the job code.
DECLARE @FromDate DATETIME = DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7-7,0)
,@ToDate DATETIME = DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7-1,0)
;
--===== Show the dates and the day of the week they occur on.
-- This is just for demo purposes.
SELECT FromDate = @FromDate
,FromDateDoW = DATENAME(dw,@FromDate)
,ToDate = @ToDate
,ToDateDoW = DATENAME(dw,@ToDate)
;Since I ran that code on 12 May 2022 (it's Thursday), it produced the following dates for the starting and ending dates of last week.
The, just use the variables in the stored procedure call instead of the literal dates.
--===== Create and populate the parameters for last week (Mon to Sun)
-- This is the part that would go into the job code.
DECLARE @FromDate DATETIME = DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7-7,0)
,@ToDate DATETIME = DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7-1,0)
;
--===== Now execute the stored procedure with those dates.
EXEC dbo.sp_getdetails @FromDate, @ToDate
;Unfortunately, that may not work the right way. While the code above is correct, we're assuming that stored procedure is going to handle those dates correctly. If it's using an unmodified BETWEEN, the the report will only be inclusive of 6 days and and maybe 3.3 milliseconds of the Sunday of that week.
@mcfarlandparkway - We NEED to see the code for the stored proc being called to make sure t that you're not hanging himself or the company you're working for.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply