on 04-18-2012 6:32 PM
Hy,
I have report with a diagram.
I have to select data from a begin date to a end date.
I type this Crystal report formula
{ihRawData.TimeStamp} >= {?Startdate} and {ihRawData.TimeStamp} <= {?Enddate}
Where ihRawData.TimeStamp is a Datetime column and Startdate/EndDate, two datetime parameters
This, give me the following SQL query that don't work
SELECT "ihRawData"."Tagname", "ihRawData"."TimeStamp", "ihRawData"."Value"
FROM "ihRawData" "ihRawData"
WHERE ("ihRawData"."TimeStamp">=CONVERT(DATETIME, '2012-04-18 14:00:00', 120) AND "ihRawData"."TimeStamp"<CONVERT(DATETIME, '2012-04-18 18:00:01', 120))
I have try with a SQL client, and a query that work is the following :
SELECT "ihRawData"."Tagname", "ihRawData"."TimeStamp", "ihRawData"."Value"
FROM "ihRawData" "ihRawData"
WHERE ("ihRawData"."TimeStamp">='2012-04-18 14:00:00' AND "ihRawData"."TimeStamp"< '2012-04-18 18:00:01')
As you can see, Crystal report add a CONVERT(DATETIME, '...') function to the sql query, that is not needed and not understood by the SQL server.
How can i solve this problem, knowing that i can't modify the SQL database.
Thanks
A little up...
May be is there any way to edit/modify the sql query ?
thanks
Adrien
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The Proficy iHistorian OLD DB driver does not understand the convert SQL syntax added to date and time parameters by Crystal Reports. Any dates and times must be passed as string and so you need to use a command as described at the following link on the GE site
http://support.ge-ip.com/support/index?page=kbchannel&id=09237d4900112023bed26075da
Hi Adrien,
Like Clive said, could you create a command object instead?
When you go the Database Expert and connect to the datasource, you should see an "Add Command" button under the connection name.
Select that and type in the SQL Query that works on the database.
For the parameters, in the same Add Command window, there is a button that lets you create parameters too. Once you create the parameters, make sure you include them in the SQL Query otherwise they disappear if they're left unused. Just double-click the parameter name and it will be inserted at the cursor position in the query.
-Abhilash
Hi,
What is the database? What is the version of the Crystal Reports? And finally, what is the database driver and connectivity method? (ODBC, OLEDB, Native)?
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.