Conversion failed converting datetime from string

I am trying to convert my three parame开发者_StackOverflow中文版ters to a DATETIME but its not working. I get the error that the conversion failed when converting datetime from character string whenever I run this query. Perhaps I am doing in wrong in the conversion? If anyone can provide any feedback.

    @month varchar,
    @day varchar,
    @year varchar

AS
DECLARE @date DATETIME
SET @date = Convert(DateTime, @month + '/' + @day + '/' + @year, 101)

Select *
From events
Where (EDate = @date) OR EDateEnd = @date OR @date Between EDate AND EDateEnd
Order By EDate ASC

---

You need to set the size of your parameters. Probably something like

@month varchar(2),
@day varchar(2),
@year varchar(4)

---

That should be working. Make sure you have provided valid values in you parameters.

Update

You should lose the 101 parameter for conversion. Provided that parameters are informed with valid values, this should work for both 2-digit and 4-digit years:

SET @date = Convert(DateTime, @month + '/' + @day + '/' + @year)

---

This is just a guess, because the conversion function shown should work with the proper parameters.

Are you passing in the year as a two-digit number? If so, try passing it as the full four digit year (which the "101" format expects) OR change it to

SET @date = Convert(DateTime, @month + '/' + @day + '/' + @year, 1) 

if you're passing in a 2 digit year.

(See the difference for with century and without century here: http://msdn.microsoft.com/en-us/library/ms187928.aspx)

EDIT

I have a second guess... The error may not be on the line where you're explicitly converting the parameters into a Datetime variable. This has burned me before... The error MAY be occurring on the following line:

Where (EDate = @date) OR EDateEnd = (@date) OR @date Between EDate AND EDateEnd 

if the EDate column or EDateEnd column is not necessaryly a DateTime column. It could be that THOSE contain the values that can't be converted to a DateTime. (They could be char fields, with a DateTime string stored in them, or they could be actual Date fields with null values stored in them.)

However, without more information about the actual schema of the database it's hard to tell. The best we can do is guess.