Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

Using SQL Server 2008, how would you modify 2 input parameters to compare them to a date field considering the 2 parms passed in contain the month and year (not the date of the month) ?

I am working with SQL Server 2008.

Is there a way to rework the following stored procedure that originally used 2 input parameters that are passed in:
I currently use 2 input parameters as follows:  a) strDateFrom (for ex: '2017-01-01) and b) strDateTo (for ex: '2017-01-31')

The purpose of these 2 parameters is to use them as the low and high values compared to a date field so that records will be created in a date range and these 2 parameters define the boundaries.

Now I would like to replace these 2 Input Parameters with 2 new Input parameters as follows:

1) strparm1 with a value representing a 2 digit month AND
2) strparm2 with a value representing a 4 digit year in format CCYY.  

For example, if strparm1 = '01' and strparm2 = '2017'

Then these 2 new parms should be translated into the values '2017-01-01' (lower date range) and '2017-01-31' (higher date range) and compared to the field dtCreated in the following stored procedure.

In other words, the month and year are passed in and based on these 2 values, a monthly report is generated for a given month. The first day of the month and the last day of the month would have to be plugged in to create the entire date value unless there is a way to inspect just the month and year values from the field dtCreated.


ALTER PROCEDURE [dbo].[proc_CSL_UndeliverableByDate] @strDateFrom NVARCHAR(10),
@strDateTo NVARCHAR(10)
AS
SELECT bank As Bank, CONVERT(VARCHAR,[chkDt],101) As 'Check Date', branch as Branch, chkNum AS 'Check Number',
accountNum AS 'Account Number', amount AS Amount, CONVERT(VARCHAR,[dtCreated],101) AS 'Journal Date'
FROM tbl_CSL_UndeliverableChecks
WHERE dtCreated >= @strDateFrom AND dtCreated <= @strDateTo
GROUP BY tbl_CSL_UndeliverableChecks.bank, ([dtCreated]), [chkDt], [branch], [chkNum], [accountNum], [amount];
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial