Datdiff in Crystal Report

[former_member736866]

I have a report in Crystal report that has a promise date and a shipped day, both data types are number. The data is stored as yyyymmdd in the database (DB2) so I created two formula fields to change the date format to display mm/dd/yyyy. I have two parameter "StartDate and EndDate" and the type is string. I need to find the difference between these two fields and display the date difference on the report.

Here is the formula fields I have created:

Formula field: Promise_date

If {Command.PROMISE_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.PROMISE_DT}/10000),
Truncate(Remainder({Command.PROMISE_DT},10000)/100),
Remainder({Command.PROMISE_DT},100))

Formula field: Shipped_day

If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))

Formula field: datediff

DateDiff("d",{@Shipped_day},{@Promise_dt})

When I enter the date in the parameter fields I have to enter it in as "yyyymmdd" for both fields then run the report. It pulls back data for the first page.
When I try to go to page two of the report I get an error "A month number must be between 1 and 12." Then the formula Editor opens for Shipped_day and highlights

If {Command.SHIP_DT} = 0 then date(0000,00,00) else
Date (Truncate({Command.SHIP_DT}/10000),
Truncate(Remainder({Command.SHIP_DT},10000)/100),
Remainder({Command.SHIP_DT},100))

[DellSC · Accepted solution]

Date(0000,00,00) is what's causing the issue - there is no such date. I would put this date either far in the future or far in the past. Then add an "If" statement around the date diff that will return 0 or null if the dates are the "dummy" date that you use. Also, here's what I'd do for the formulas:

{@Promise_Dt}
If {Command.PROMISE_DT} = 0 then 
  date(1970,01,01) 
else
  Date (Truncate({Command.PROMISE_DT}/10000),Truncate(Remainder({Command.PROMISE_DT},10000)/100),Remainder({Command.PROMISE_DT},100))

{@Shipped_Day}
If {Command.SHIP_DT} = 0 then 
  date(1970,01,01) 
else
  Date (Truncate({Command.SHIP_DT}/10000),Truncate(Remainder({Command.SHIP_DT},10000)/100),Remainder({Command.SHIP_DT},100))

{@DateDifference}
if {@Promise_dt} > date(1970,01,01) and {@Shipped_day} > date(1970,01,01) then
  DateDiff("d",{@Shipped_day},{@Promise_dt})
//below is optional
else 0

-Dell

[former_member736866 · Accepted solution]

Thanks dell.stinnett-christy that resolved the issue I was having. Now I am trying to create two more formula fields: OnTime and Late using the date from the DateDiffence formula.

OnTime formula:

if {@dateDiff} > 0 then Count ({@dateDiff})

I get 0.00 for this formula ( I am trying to get all numbers that are greater than 0)

Late formula:

if {@dateDiff} < 0 then Count ({@dateDiff})

For this formula I get all the records ( I am trying to get all number lower than zero)

[DellSC · Accepted solution]

Unfortunately, you can't use Count() like that. You have two options:

1. If you need to show the count at the end of the report, use a Running Total. This is the easiest way to do this and it looks something like this:

Field to Summarize: {@dateDiff}
Evaluate: Use a formula

{@dateDiff} > 0

Reset: Never

2. If you need to show the count at the top of the report, you'll need to use a formula that will identify the records to count. Something like this:

If {@dateDiff} > 0 then 1 else 0

Then you would use Sum() instead of Count() to get the count you're looking for.

-Dell

[former_member736866 · Accepted solution]

I need to have it like the second option because I need the data at the top of the report. I don't understand how you saying to to do it. I do understand that I can not use count in the formula.

[DellSC · Accepted solution]

The formula I provided returns a 1 if the difference > 0 and 0 if it's less than or equal to 0. If you want to count the records that have non-0 differences - either greater than 0 or less than 0 - you'll have to make a minor change to the formula - like this:

{@NotZero}
If abs({@dateDiff}) > 0 then 1 else 0

Then, you'll use a Sum of {@NotZero} to count the non-0 records. To do this, click on the "Summary" button in the button bar (looks like a capital E) or go to the Insert >> Summary menu option, select {@NotZero) as the field to summarize, select Sum as the aggregation, set it to be a grand total, and click on OK. The summary will appear in bold in the report footer section. Move it to where you need it.

-Dell

[former_member736866 · Accepted solution]

Thanks again Dell Stinnett-Christy I was able to get both formulas to work, you are the BEST!!!!

[DellSC · Accepted solution]

I'm glad I was able to help!

When you get a chance, please accept this answer as the correct one to close out the question. Thanks!

-Dell

[former_member736866 · Accepted solution]

I'm not sure if I did it correct. Do I just acept the best answer?

[vitaly_izmaylov]

try to change:

"date(0000,00,00)"

to

"date(1900,01,01)"