Solved

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

Posted on 2008-06-16
8
217 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
  • 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now