zimmer9
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_Undelivera bleByDate] @strDateFrom NVARCHAR(10),
@strDateTo NVARCHAR(10)
AS
SELECT bank As Bank, CONVERT(VARCHAR,[chkDt],10 1) 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_UndeliverableCheck s
WHERE dtCreated >= @strDateFrom AND dtCreated <= @strDateTo
GROUP BY tbl_CSL_UndeliverableCheck s.bank, ([dtCreated]), [chkDt], [branch], [chkNum], [accountNum], [amount];
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_Undelivera
@strDateTo NVARCHAR(10)
AS
SELECT bank As Bank, CONVERT(VARCHAR,[chkDt],10
accountNum AS 'Account Number', amount AS Amount, CONVERT(VARCHAR,[dtCreated
FROM tbl_CSL_UndeliverableCheck
WHERE dtCreated >= @strDateFrom AND dtCreated <= @strDateTo
GROUP BY tbl_CSL_UndeliverableCheck
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.