Avatar of gsszuber
gsszuber
 asked on

Problem with a SQL Command in Crystal Reports

This expression in my SQL Command seems to be gumming up the works:

CONVERT(VARCHAR(8), DATEADD(year, -1, {?BeginDate}), 112)

I get the attached error message.

The funny this is that if I simply replace {?BeginDate} with a string value like '20091001', everything works like a charm. This makes no sense since they're both strings and should give the same result.

Any ideas?
Error.png
DB Reporting ToolsCrystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
mabbj747

Is your BeginDate is varchar. It seems like that you have some garbade data in the colum. Try to run a query to convert the column values in datetime and then you will get the similiar error as it seems that SQL cannot convert the values to datetime type data type due to the data in the table.
ASKER CERTIFIED SOLUTION
gsszuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
James0628

If {?BeginDate} is a string parameter and you're using it in a CR command, then it needs to be in single-quotes.

CONVERT(VARCHAR(8), DATEADD(year, -1, '{?BeginDate}'), 112)


 When CR executes the command, it just replaces the parameter with its value.  Without the quotes, you'd get something like:

CONVERT(VARCHAR(8), DATEADD(year, -1, 20091001), 112)

 In that case, 20091001 is an int, and you'll get the error you posted.

 Your alternative works because the use of LEFT and RIGHT forces SQL to convert the int value to a string.

 James
gsszuber

ASKER
James, I'm really sorry I didn't give assign you points on this question. I guess I must not have seen your response before I posted how I resolved it on my own. What's really a shame is that your solution is the better and more correct one.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
James0628

No problem.  I'm glad you saw the post, because I find it's always better to know why something didn't work, than to just stumble on another approach that works, without really understanding why.

 If you really want, you could ask to have the question re-opened, and then re-close the question and re-assign the points (you could probably use the "Request Attention" button for that).  But there's no need on my account.  I'm OK with the way things are, although, for anyone that might look at this question later, I guess there is something to be said for having the better solution flagged as a solution.  But the fact that you mentioned that in your post may be enough to accomplish that.

 James