DateTime Conversion with parameter

[WoodyGuthrie]

I have input parameters @SDate and @EDate for my date range. The date being compared is a date/time and so I do a conversion to (nvarchar(20),101) to get mm/dd/yyyy. The problem is sometimes the user enters the date parameters as mm/dd/yy. In this case it works fine for @SDate but the range is not matched as intended for @EDate. It pulls data from one day less than the intended End Date of the range. Any suggestions how to better handle this?

Thanks.


WHERE (CONVERT(nvarchar(20),DteDate, 101)>= @SDate
AND (CONVERT(nvarchar(20), DteDate, 101) <= @EDate) AND...


Woody

 

[RiverGuy]

Here's how I approach these situations.

1. The parameters are defined as DateTime -NOT- varchar.
2. It is the application's responsibility to pass in a DateTime value instead of a string with a variable format
3. Quite often, I include logic to alter the effective parameters in the stored procedure. A case in point is the fact that many applications make use of DateTime controls and sometimes pass in a time. If you're just looking for date, then you can do something like the following:

DECLARE @Start DATETIME 
SELECT @Start = CONVERT(DATETIME, CONVERT(CHAR(10), @SDate, 112))
DECLARE @End DATETIME
SELECT @End = CONVERT(DATETIME, CONVERT(CHAR(10), @EDate, 112))

SELECT * FROM SomeTable WHERE DateColumn BETWEEN @Start AND @End

The code example above takes off the time portion of the supplied DateTime parameters in case the application supplied a date with a control which includes time.