Link to home
Start Free TrialLog in
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
Avatar of mabbj747
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
Avatar of gsszuber
gsszuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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
Avatar of 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.
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