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

asked on

I am using SQL Server 2008. Is there a way to pass in 2 parameters strings in the format MM/DD/YYYY and convert these strings into a DATE data type using SQL Server 2008 and then make a date compare?

I am using SQL Server 2008. Is there a way to pass in 2 parameters strings in the format MM/DD/YYYY and convert these strings into a date data type using SQL Server 2008 and then make a date comparison. I don't know if casting would help.

I tried the following execute statement on the stored procedure below:

exec proc_CSL_UndeliverableByDate '03/07/2003',  '03/07/2003'

but no records are returned in the result set although I know there are records with a value in the "dtCreated" field
of '2003-03-07'.

USE [CSL]
GO
/****** Object:  StoredProcedure [dbo].[proc_CSL_UndeliverableByDate]    Script Date: 01/12/2017 20:11:48 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[proc_CSL_UndeliverableByDate] @strbegdate nvarchar(10) , @strenddate nvarchar(10) AS
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SELECT @startDate = CONVERT(DATETIME, @strbegdate, 101)
SELECT @endDate = CONVERT(DATETIME, @strenddate, 101)

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 >= @startDate  AND dtCreated < @endDate
GROUP BY tbl_CSL_UndeliverableChecks.bank, ([dtCreated]), [chkDt], [branch], [chkNum], [accountNum], [amount];
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi Zimmer,
Please try this..

You have to use YYYY-MM-dd format..

USE [CSL]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[proc_CSL_UndeliverableByDate] 
(
	@strbegdate nvarchar(10) , @strenddate nvarchar(10)
)
AS

BEGIN

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SELECT @startDate = CONVERT(DATETIME, @strbegdate)
SELECT @endDate = CONVERT(DATETIME, @strenddate)

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 >= @startDate  AND dtCreated < @endDate 
GROUP BY tbl_CSL_UndeliverableChecks.bank, ([dtCreated]), [chkDt], [branch], [chkNum], [accountNum], [amount]

END

Open in new window


Hope it helps!
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore 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
ASKER CERTIFIED SOLUTION
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