Solved

How would you rewrite a stored procedure to accept 2 date parameters to create a date range ?

Posted on 2008-06-16
8
226 Views
Last Modified: 2013-12-05
I am developing an Access 2003 applicaton using Access as the front end and SQL Server as the back end database.

I currently use a stored procedure named procFlRedFlag which accepts a single date parameter named @RptYear as per the following and I hard code the MM and DD value as -06-30:
In the following MutFund is a integer variable (for ex: MutFund can be 1, 2, 3, 4, or 5).

CREATE PROCEDURE dbo.procFlRedFlag
@RptYear int
AS
...
 (P .PropertyType='OTHER' AND C.DateLost <= CAST((@RptYear - MutFund) AS VARCHAR) + '-06-30' AND LEN(P.IRACode) = 0)

As you can see, I extract records based on this stored procedure where the C.DateLost value
is <= the (RptYear - MutFund) '-06-30'. Thus, if RptYear is 2003 and MutFund is 3, then I would extract records where
C.DateLost <= 2003 - 3 '-06-30' which translates to C.DateLost <= 2000-06-30.

****************************************************************************
Instead of using @RptYear, I would like to use 2 Date Parameters as a "From Date" and a "To Date".
I would use these 2 date parameters in the following:

With com
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procFlRedFlagDate"
       .Parameters.Append .CreateParameter("DteFrom", adDate, adParamInput, , CDate(txtDateFrom.Value))
       .Parameters.Append .CreateParameter("DteTo", adDate, adParamInput, , CDate(txtDateTo.Value))
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
End With
----------------------------------------------------------------------
CREATE PROCEDURE dbo.procFlRedFlagDate
@DteFrom datetime, @DteTo datetime
AS

How would you revise the following statement within the stored procedure to incorporate the DteFrom and DteTo parameters ?  [C.DateLost >= DateFrom and C.DateLost <= DateTo]

(P .PropertyType='OTHER' AND C.DateLost <= CAST((@RptYear - MutFund) AS VARCHAR) + '-06-30' AND LEN(P.IRACode) = 0)


0
Comment
Question by:zimmer9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:zx10r
ID: 21795571
Why not just use between.   WHERE C.DateLost BETWEEN @DteFrom AND @DteTo
0
 

Author Comment

by:zimmer9
ID: 21795691
Because there is an integer variable named MutFund representing years that needs be factored in.
Thus, WHERE C.DateLost BETWEEN @DteFrom - MutFund AND @DteTo - MutFund

MutFund needs to be subtracted from both @DteFrom AND @DteTo.
Thus, if MutFund = 3 (years), then the date range would be:
C.DateLost BETWEEN @DteFrom - 3 (as in years) AND @DteTo - 3 (as in years).
                                                                                   
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21795894
Concur...
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:zimmer9
ID: 21796743
I know that the parameter @RptYear which is a 4 digit year (YYYY) minus an integer variable (MutFund) works correctly.

How would you code the following ?
 
WHERE C.DateLost BETWEEN
@DteFrom, which is in the format MM/DD/YYYY MINUS an integer variable, MutFund which represents a year with a value from 1 to 5
0
 
LVL 4

Expert Comment

by:zx10r
ID: 21797175
Use the DATEADD function to trim off the number from your date.

DATEADD(YEAR,-@MutFund,@DteFrom)
0
 
LVL 4

Accepted Solution

by:
zx10r earned 500 total points
ID: 21797254
Sorry,  I meant to subtract the year from an integer value and not trim.
0
 

Author Comment

by:zimmer9
ID: 21798159
So how would you convert from:
(P .PropertyType='OTHER' AND C.DateLost <= CAST((@RptYear - MutFund) AS VARCHAR) + '-06-30' AND LEN(P.IRACode) = 0)

to something like:
(P .PropertyType='OTHER' AND C.DateLost Between DATEADD(YEAR,-@MutFund,@DteFrom)
AND DATEADD(YEAR,-@MutFund,@DteTo)
AND LEN(P.IRACode) = 0)
 
0
 

Author Comment

by:zimmer9
ID: 21798181
MutFund is a field name of integer type, not a parameter.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question