DateTime convertion in SQL problem

[Former Member]

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

[Former Member]

A little up...

May be is there any way to edit/modify the sql query ?

thanks

Adrien

[Former Member]

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

[abhilash_kumar]

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

[abhilash_kumar]

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

[Former Member]

Thank you for the really rapid answer and sorry for the missing details :

database : proficy Historian (it's a specific database used for signal data aquisition in industry)

version of CR : XI

driver : OLEDB

Thanks

Adrien