Parameter : Prompt the Date and Ignore Time.

[antonx]

Hi,

CR 10
Oracle Ora10gR2

I have 2 parameters {?Start_Date} and {?End_Date} with the following record selection criteria:

(
If {?Database_Name} <> 'ALL'
then {BACKUP_HIST_V1.DB_NAME} = {?Database_Name}
else if {?Database_Name} = 'ALL'
then true
)
and
(
if {?Start_Date} <> date(9999,9,9)
then {BACKUP_HIST_V1.START_TIME} >= {?Start_Date}
else if {?Start_Date} = date(9999,9,9)
then true
)
and
(
if {?End_Date} <> date(9999,9,9)
then {BACKUP_HIST_V1.END_TIME} <= {?End_Date}
else if {?End_Date} = date(9999,9,9)
then true
)


{BACKUP_HIST_V1.START_TIME} and {BACKUP_HIST_V1.END_TIME} are ORACLE table fields with datatype DATETIME such as 2008/02/03 4:49:02PM.

The parameters {?Start_Date} and {?End_Date} have a Value type: Date - because the user only wants to be prompted for the START DATE and END DATE.

When I run the report with the following discrete values:

Database_Name -> 'All'
Start_date -> 2008/02/03
End_date -> 2008/02/04

I get the following results which are incorrect:

DB Name START_DT END_DT
AAA 03/FEB/08 22:11:05 03/FEB/08 22:29:31
BBB 03/FEB/08 22:31:56 04/FEB/08 00:14:04
CCC 03/FEB/08 22:50:53 03/FEB/08 23:09:18
DDD 04/FEB/08 16:49:25 04/FEB/08 18:40:43
EEE 04/FEB/08 16:49:57 04/FEB/08 19:01:18

According to the record selection criteria above, records DDD and EEE should not be displayed because the DATE must be between 2008/02/03 AND 2008/02/04.

However if I change the value type of the parameters from Date to DateTime, my results will be correct. If I change it to DateTime, it will prompt the user to enter the time along with the date. The user does not want to enter the time, only the date.

How do I go about fixing this.

Thanks

 

[IdoMillet]

Looks like these two records SHOULD be included in the selection based on your own logic. Please clarify in what way the violate the intended logic.

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[antonx]

Hi,

No, those 2 records SHOULD NOT be included based on the record selection. I have an AND condition in the record selection.

PARAMETERS:
Start_date -> 2008/02/03
End_date -> 2008/02/04

RESULTS:
DB Name {BACKUP_HIST_V1.START_TIME} {BACKUP_HIST_V1.END_TIME}
DDD 04/FEB/08 16:49:25 04/FEB/08 18:40:43
EEE 04/FEB/08 16:49:57 04/FEB/08 19:01:18

The DATE must be between 2008/02/03 AND 2008/02/04

{BACKUP_HIST_V1.START_TIME} and {BACKUP_HIST_V1.END_TIME} are ORACLE table fields with datatype DATETIME.

The parameters {?Start_Date} and {?End_Date} have a Value type: Date - because the user only wants to be prompted for the START DATE and END DATE (not the TIME).

Looks like the results are incorrect because you can't compare datatype DATETIME with datatype DATE.

Thanks.

 

[IdoMillet]

Sounds like you need to change
{BACKUP_HIST_V1.END_TIME} <= {?End_Date}
to
{BACKUP_HIST_V1.END_TIME} < {?End_Date}

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[MJRBIM]

Whenever I have this kind of parameter prompt date-only request on a DATE-TIME field, I use a formula in the record selection criteria.

PARAMETERS
==========
{?PARAM_DATE_START} = DATE
{?PARAM_DATE_END} = DATE

FORMULAS
========

@TIME_MIDNIGHT
Time (00,00,00)

@TIME_MIDNIGHT_MINUS_SECOND
Time (23,59,59)

@PARAM_DATE_TIME_START
DateTime ({?PARAM_DATE_START},{@TIME_MIDNIGHT})

@PARAM_DATE_TIME_END
DateTime ({?PARAM_DATE_END},{@TIME_MIDNIGHT_MINUS_SECOND})

RECORD SELECTION
================

({Table.Date} >= {@PARAM_DATE_TIME_START} AND {Table.Date} <= {@PARAM_DATE_TIME_END})

This will give you all records between Midnight on your START_DATE and 11:59:59 PM on your END_DATE.

 

[antonx]

I tried MJRBIM's suggestion and I am still getting the same results.

RESULTS:
DB Name {BACKUP_HIST_V1.START_TIME} {BACKUP_HIST_V1.END_TIME}
DDD 04/FEB/08 16:49:25 04/FEB/08 18:40:43
EEE 04/FEB/08 16:49:57 04/FEB/08 19:01:18

The DATE must be between 2008/02/03 AND 2008/02/04. It looks like the AND in my record selection criteria is not working properly.

Why is the START_TIME in my results displaying 04/FEB/08. I don't get it! It may be the ORACLE database - the way it stores DATETIME fields.

Any ideas?

Thanks

 

[antonx]

Here is my SHOW SQL in Crystal Reports:

SELECT "BACKUP_HIST_V1"."START_TIME", "BACKUP_HIST_V1"."END_TIME", "BACKUP_HIST_V1"."TIME_TAKEN_DISPLAY",
"BACKUP_HIST_V1"."OUTPUT_BYTES_DISPLAY", "BACKUP_HIST_V1"."OUTPUT_BYTES_PER_SEC_DISPLAY",
"BACKUP_HIST_V1"."DF_SIZE", "BACKUP_HIST_V1"."AL_SIZE",
"BACKUP_HIST_V1"."INC_LEVEL",
"BACKUP_HIST_V1"."DB_NAME"

FROM "XXSIRIS"."BACKUP_HIST_V1" "BACKUP_HIST_V1"

WHERE
"BACKUP_HIST_V1"."START_TIME">={ts '2008-02-03 00:00:00'} AND
"BACKUP_HIST_V1"."END_TIME"<{ts '2008-02-05 00:00:00'}

ORDER BY "BACKUP_HIST_V1"."START_TIME"

Even creating a new report and adding this SQL statement in the Add Command of Crystal Reports, I still get the same results.

 

[MJRBIM]

The formulas I suggested are for comparison against a SINGLE field {Table.Date} in your database - in your example it would only be compared against {BACKUP_HIST_V1.START_TIME}.

Does it really matter when the backup ends...?

If not, you can compare the parameters against the {BACKUP_HIST_V1.START_TIME} field.

Otherwise, if you want to compare against two (2) different fields in your database, you need two-different sets of Start and End date parameters for a total of four (4) parameters...

PARAMETERS
==========
{?PARAM_BACKUP_START_DATE_START} = DATE
{?PARAM_BACKUP_START_DATE_END} = DATE

{?PARAM_BACKUP_END_DATE_START} = DATE
{?PARAM_BACKUP_END_DATE_END} = DATE

RECORD SELECTION
================

({BACKUP_HIST_V1.START_TIME} >= {?PARAM_BACKUP_START_DATE_START} AND {BACKUP_HIST_V1.START_TIME} <= {?PARAM_BACKUP_START_DATE_END})

AND

({BACKUP_HIST_V1.END_TIME} >= {?PARAM_BACKUP_END_DATE_START} AND {BACKUP_HIST_V1.END_TIME} <= {?PARAM_BACKUP_END_DATE_END})