0

I need help with this Conversion failed when converting date and/or time from character string.

I'm trying to run a simple query on an @as_of_date, 10 days ago and 30 days ago. The query works fine if I run it on 3-5 accounts, but any more than that if fails. How do I change my query so that I don't get the conversion error?

Here's my sample query: enter image description here

Edit

Also tried removing the quotes from the select statement. Still doesn't work.

enter image description here

Improve this question
5
  • 1
    you are attempting to convert a string "@as_of_date" instead of value of variable with that name. remove quotes. Commented Feb 27, 2016 at 19:44
  • 3
    Why on earth are you typing mm-dd-yyyy instead of a format that can't be misinterpreted by regional/language settings, like yyyymmdd? Please read this in full. Also, show all of the code, as I don't believe the error is happening in the portion you've shown. Commented Feb 27, 2016 at 22:09
  • I think my issue is much larger. My query will run fine for the first 1370 records, but when I change the query to the top 1371 it throws the "conversion failed when converting date and/or time from character string" message. I'm also not able to order by any column. I think it's a problem with the table and have sent a message to the developer. Commented Feb 28, 2016 at 5:27
  • @Showstopper as @Aaron said you've to change this line CONVERT(date,'02-25-2015',110) to CONVERT(date,'2015-2-25',110). This one is correct format. Commented Feb 28, 2016 at 7:00
  • 1) @as_of_date actually is of type date so no conversion needed 2) you are hiding problematic part of query Commented Feb 28, 2016 at 7:16

3 Answers 3

Reset to default
1

You can try using ISDATE function to see the input data is a valid date.. Also I don't see in the screenshot how/what is the input.

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

1 Comment

The ISDATE function fixed the issue. Thank you!
0

You are converting the @as_of_date twice. You can use SELECT convert(date, getdate(), 110) Correct me if I am wrong.

Improve this answer

1 Comment

Sorry, this isn't working. I removed the convert and just used 'currentday' = @as_of_date and i still get the conversation failed error message.
0

please remove quotes for @asofdate variable in select statement and try like convert(date,@as_of_date,110)

Note:While assigning the value to the variable only its converting ,so no need to convert once again in select statement

Improve this answer

1 Comment

Sorry, this didn't work. See my edited version above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.