Print Page | Close Window

Dates in SQL command

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21242
Printed Date: 29 Mar 2024 at 3:58am


Topic: Dates in SQL command
Posted By: EMStr
Subject: Dates in SQL command
Date 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?
 
 
 
 



Replies:
Posted By: lockwelle
Date 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



Print Page | Close Window