• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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

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
zimmer9
Asked:
zimmer9
  • 4
  • 3
1 Solution
 
zx10rCommented:
Why not just use between.   WHERE C.DateLost BETWEEN @DteFrom AND @DteTo
0
 
zimmer9Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
Concur...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
zimmer9Author Commented:
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
 
zx10rCommented:
Use the DATEADD function to trim off the number from your date.

DATEADD(YEAR,-@MutFund,@DteFrom)
0
 
zx10rCommented:
Sorry,  I meant to subtract the year from an integer value and not trim.
0
 
zimmer9Author Commented:
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
 
zimmer9Author Commented:
MutFund is a field name of integer type, not a parameter.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now