| Author |
Message |
newbie
Newbie
Joined: 10 Jul 2007
Online Status: Offline
Posts: 29
|
   Topic: Dealing with Dates
Posted: 24 Oct 2007 at 8:02am |
Hello,
I have a report based on the tables of Oracle Database. I created a "DATE_ENDING" parameter as "DATE" datatype.
The Report Selection Formula looks like this.
( {CHECK_MASTER.CHECK_DATE} <= {?Date_Ending}) AND
( IIF(IsNull({CHECK_MASTER.CHECK_VOID_DATE}),'01/01/3000',totext({CHECK_MASTER.CHECK_VOID_DATE})) > totext({?DATE_ENDING})) AND
( IIF(IsNull({CHECK_MASTER.CLEARED_DATE}),Cdate('01/01/3000'),{CHECK_MASTER.CLEARED_DATE}) > {?DATE_ENDING})
But I'm getting only one condition in the SQL Query like below with Date parameter with Timestamp.
WHERE "CHECK_MASTER"."CHECK_DATE"<{ts '2007-10-25 00:00:00'}
How to remove the timestamp and IIF statement is also not working. How to resolve this?
Thanks
VK
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 24 Oct 2007 at 12:24pm |
I would break it down into a couple of SQL Expression field and then use those in your selection criteria. The SQL Expressions will allow you to use Oracle-specific functions to handle some of this.
Your expressions would look something like this:
VoidDate
trunc(nvl("CHECK_MASTER"."CHECK_VOID_DATE", To_Date('01-JAN-3000')))
MasterClearedDate
trunc(nvl("CHECK_MASTER"."CLEARED_DATE", To_Date('01-JAN-3000)))
Your Selection Formula would then look something like this:
{CHECK_MASTER.CHECK_DATE} <= {?Date_Ending} and
{%VoidDate} > {?Date_Ending} and
{%MasterClearedDate} > {?Date_Ending}
As for the time on the end of your date, is the type of your parameter "Date" or "Date Time". I suspect it's Date Time, but you want it to be Date.
-Dell
|
|
|
|
IP Logged |
|
neevak
Newbie
Joined: 25 Oct 2007
Online Status: Offline
Posts: 3
|
Posted: 25 Oct 2007 at 8:24am |
|
I have a similar question. When using select expert i want to get records that are within 24 hours of the run time. So I basically want to run the report at 7AM and get all data from 7Am the previous day to now. I am using CurrentDateTime - 1...but that is obviously wrong. I am new to CR. Can you please help me?
|
|
IP Logged |
|
newbie
Newbie
Joined: 10 Jul 2007
Online Status: Offline
Posts: 29
|
Posted: 25 Oct 2007 at 2:33pm |
Thanks Dell, that worked for me.
One small problem still exists :- In Report Selection Formula
({CHECK_MASTER.CHECK_DATE} <= {?Date_Ending})
this shows in Show SQL Query as
"CHECK_MASTER"."CHECK_DATE"< {ts '2007-08-26 00:00:00'}
The EQUAL sign is missing in SQL Query. Is there any reason?
Thanks
VK
|
|
IP Logged |
|
wattsjr
Groupie
Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 25 Oct 2007 at 5:08pm |
Hi neevac,
You're on the right track "CurrentDateTime - 1" does return a value of 24 hours prvious to the run time.
In Select Expert choose your {databaseTable.Field} and use/select the "Formula:" option.
In the formula area use this format:
{databaseTable.Field} >= CurrentDateTime - 1
This sould work for you.
Regards,
|
|
-jrw
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 26 Oct 2007 at 6:29am |
I don't know why the '=' would be missing.... Remove that condition from the Select expert, close the expert, reopen it, and try adding it again.
-Dell
|
|
|
|
IP Logged |
|
neevak
Newbie
Joined: 25 Oct 2007
Online Status: Offline
Posts: 3
|
Posted: 26 Oct 2007 at 11:18am |
|
thanks wattjr. I tried that but it just returned records for the current day only, nothing from the previous day. :( any other ideas?
|
|
IP Logged |
|
wattsjr
Groupie
Joined: 25 Jun 2007
Location: United States
Online Status: Offline
Posts: 51
|
Posted: 26 Oct 2007 at 1:27pm |
Hi Neevak,
I'm not sure what the problem is. It works when I test it against my data.
Normally, when I have a problem like this, I like to see what data is actually coming in. Why don't you change the Selection criteria to use the "is greater than" option and use a date of several days ago (like 10/20/2007) just to make sure that the data is there.
Also, is the date field in your records in date/time format, or just date format? If you aren't sure, try to format the date in the detail line and see what your choices are.
By the way, what is the type of database you're using?
|
|
-jrw
|
|
IP Logged |
|
newbie
Newbie
Joined: 10 Jul 2007
Online Status: Offline
Posts: 29
|
Posted: 26 Oct 2007 at 1:39pm |
Dell,
I did whatever you said, but no use. I did a test, replacing "<=" with ">=", "Greater Equal" sign appears perfect in SQL Statement.
"CHECK_MASTER"."CHECK_DATE">={ts '2007-08-17 00:00:01'}
Did anyone experience the same problem and how to resolve it?
Neevak, you need to start a new thread.
Your help request could go unnoticed becuse it is buried within somemone elses thread and also makes confuse who reads this thread.
Thanks
VK
|
|
IP Logged |
|
newbie
Newbie
Joined: 10 Jul 2007
Online Status: Offline
Posts: 29
|
Posted: 29 Oct 2007 at 8:10am |
Hello All,
I found the an article on the internet about the Date Parameters.
Date parameters in a command with DateTime Fields:
There is always a risk when you use Date parameters to query DateTime fields. Because a Date parameter has no time value, the query may use 12:00am for both the start and end dates of the query. If all of your times are stored as 12:00am then this isn't a problem. But if your field has true time stamps, than any DateTime value on the last date in the range would be skipped over.
Crystal fixes this problem automatically when it generates the SQL for most reports. It automatically adds one day to the end date of the parameter range and then uses a "less than" operator in the SQL. This way it finds any value before that next day starts at 12:00am.
However, if you are using Command objects you are entering in your own SQL, so you have to make your own adjustment. Fortunately, this is easy to do. You put your command parameter into the WHERE clause of the SQL and add one day to it, changing the comparison operator to a "Less Than". The WHERE clause would look something like this:
WHERE ("TABLE"."DATE" >= {?Begin}
AND "TABLE"."DATE" < {?End} + 1 )
Note that the first "greater than" operator uses the equal sign but the second operator doesn't. Of course, if you created DateTime parameters instead of Date parameters this problem doesn't occur. It is just that most people prefer not to enter times.
Thanks for all who helped me.
Thanks
VK
|
|
IP Logged |
|
|
|
