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"
.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)
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"
.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)
Why not just use between. WHERE C.DateLost BETWEEN @DteFrom AND @DteTo
ASKER
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).
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).
Concur...
ASKER
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
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
Use the DATEADD function to trim off the number from your date.
DATEADD(YEAR,-@MutFund,@Dt
DATEADD(YEAR,-@MutFund,@Dt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,@Dt
AND DATEADD(YEAR,-@MutFund,@Dt
AND LEN(P.IRACode) = 0)
(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,@Dt
AND DATEADD(YEAR,-@MutFund,@Dt
AND LEN(P.IRACode) = 0)
ASKER
MutFund is a field name of integer type, not a parameter.