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
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
Last Comment
James0628
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
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.
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
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
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
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