Solved

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

Posted on 2008-06-16
8
220 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

856 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