Tips and Tricks
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Tips and Tricks
Message Icon Topic: Dates in SQL command Post Reply Post New Topic
Author Message
EMStr
Newbie
Newbie


Joined: 25 Nov 2014
Online Status: Offline
Posts: 2
Quote EMStr Replybullet Topic: Dates in SQL command
    Posted: 31 Dec 2014 at 12:35am

Hi, I have a problem and I hope to find a solution...

I'm working on Crystal Reports 2008 with an Oracle database. In the report is a SQL-command (a complicated union) used. To prevent this command transferring all the data from the server I have
- moved this logic to a sub-report to be able to use parameters
- introduced some parameters in main-report to carry on DateTime
- created corresponding parameters to the sub-report
 
I tested this by placing those parameters in the sub-report header, they print just fine.
 
Now, I'm trying to modify the SQL-command. I have added a where-clause to test the date like this ... WHERE STARTTIME>={ts '{?PmBeginTime}'}
The trouble is, when I click OK, the sub-report prompts to give PmBeginTime (it shouldn't do this?)
The worst thing here is this: although I have defined in options/fields of the sub-report that the dateTimes and dates must be in ISO-format (yyyy-mm-dd), the prompt does not accept anything but yyyy/mm/dd -format. If I give the dates in this format Oracle database returns an error. In either case, my parameters disappear and the sql command resets to the previous state.
 
How should I give those dateTime-parameters to it?
 
 
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Sep 2020 at 11:52am
way late, and perhaps this will help someone...
Use To_Date in the command, that should fix that part.

You also need to link the subreport parameter to the main report value. This is done via the Change SubReport Link...right click the subreport and you should see it.

HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.