Skip to Content
author's profile photo Former Member
Former Member

DateTime convertion in SQL problem

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Apr 18, 2012 at 05:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Apr 19, 2012 at 09:44 AM

    A little up...

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

    thanks

    Adrien

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.