Hey everyone,
I have a very simple report that get's it data from an MS SQL 2005 Server via a Crystal Report command:
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted IS NOT NULL
This works fine, and the results are the same whether I run the report or if I run the SQL on the server directly. My issue is that the moment I try to add a date paremeter, the command returns 0 as the count. If I hardcode dates in, it works fine... but data parameters just don't work.
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '2011/01/01' AND '2012/12/31'
This returns '42' in both the report and against the SQL database directly.
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN '{?Start}' AND '{?End}'
This causes an error: failed to retreive data, incorrect syntax near '2012'
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN {?Start} AND {?End}
This doesn't cause an error, but it returns '0' for the count
SELECT COUNT(*) FROM Files_ WHERE DATE_Submitted BETWEEN CONVERT(VARCHAR,{?Start},111) AND CONVERT(VARCHAR,{?End},111)
Here I was trying to force the date parameters into strings, but it also returned '0' for the count.
Nothing I've tried works. Does anyone know how to get this to work... Even using strings for parameters doesn't work. Again, if I hardcode the data values in the command, it works, but I need these values to be parameters... I would greatly appreciate any help that you guys could offer. Thanks!
- Scott
Edited by Chronicide - 19 Jun 2012 at 4:22am