Skip to Content
0
Former Member
Apr 18, 2012 at 05:32 PM

DateTime convertion in SQL problem

227 Views

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