Conversion failed converting datetime from string

Question

I am trying to convert my three parameters 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

Your Where expression does confuse me a little bit. Where is the difference between '(EDate = @date)' and 'EDateEnd = (@date)'? — Reporter
– Reporter, Commented Apr 21, 2011 at 13:46

Mikael Eriksson · Accepted Answer · 2011-04-21 13:42:21Z

3

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

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

answered Apr 21, 2011 at 13:42

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adriano Carneiro · Accepted Answer · 2011-04-21 13:40:37Z

0

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)

answered Apr 21, 2011 at 13:40

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Comments

David · Accepted Answer · 2011-04-21 13:45:52Z

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.

Collectives™ on Stack Overflow

Conversion failed converting datetime from string

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related