How to create a Date Diff formulae in Crystal when domparing two different

[Former Member]

I am trying to create a formulae that allows me to find the difference between dates in two different fields. Originalyy I was stuck being able to link an Invoice to a Purchase order. The AP invoice could have been copied from either a PO or a Goods receipt. I solved that problem by using the PCH1 file and linking it to the PDN1 file or the POR1 file based on if the Base Ref Type was a GR = 20 or a PO =22.

I have been able to link back to the DocDueDate in the PO using that method. I now want to find the difference between that and the TAX Date on the invoice? My formulae works fine when I am dealing with just one table. When I introduce the alias table I do not get the correct answer and have tried to determine how to tell the formulae to use the correct date from OPOR file if it is a GR = 20 or a PO =22.

Here is my original formula.

DateTimeVar d1 := {OPOR.DocDueDate};

Local DateTimeVar d2 := {OPCH.TaxDate};

DateDiff ("d", d1, d2)

I am not sure how to tell it when d1=20 use {OPOR.DocDueDate}; or when d1=22 use {OPOR_1.DocDueDate

Any help in this would be appreciated.

Thanks

Steve

[senthil_m · Accepted solution]

Sample query

SELECT T0.[DocDueDate],t1.taxdate,DATEDIFF ( dd , T0.[DocDueDate], t1.taxdate )as 'Date Diff' 
FROM OPOR T0 , OPCH T1

For your reference

http://www.crystalkeen.com/articles/crystalreports/datedifferences.htm

http://www.minisolve.com/TipsDifDatesCrystal.htm

http://blog.crystalreportsbook.com/date-functions-explained

[Former Member · Accepted solution]

senthil

Thanks for your input but you answer I think will work if I am comapring two different dates. In reality one of my dates could have two different tables to pull from.

If PCH1 Basic ref type = 20 or GR then I need to pull from the OPOR DocDueDATE or

If PCH1 Basic ref type = 22 or PO then I need to pull from the OPOR_1. DocDueDATE

Appreciate the references since I constructed thge first formula using these references

Steve

[senthil_m · Accepted solution]

Post your exact query. Base type is GRPO document and Base Ref is the GRPO document Number.

You can get the report.

[Former Member · Accepted solution]

This is the formula I am using to get the PO #

I am trying to modify this formula to pull the correct doc date based on how I linked the fields.

DateTimeVar d1 := {OPOR.DocDueDate};

Local DateTimeVar d2 := {OPCH.TaxDate};

DateDiff ("d", d1, d2)

AAgain I created an alias to link the files and I am using {OPOR.DocDueDate};and {OPOR_1.DocDueDate};

[msundararaja_perumal · Accepted solution]

Hi,

These are the two forms of date difference formula availabe in crystal reports,below is in the cystal syntax format

DateDiff(intervalType,startDateTime,endDateTime)

where interval type can be "yyyy" (Years), "q" quarters, "m" (months), "d"(days)

or

Datediff(intervalType,startDateTime,endDateTime,firstDayOfWeek)

where intervaltype can be "ww"

Ex.DateDiff ("ww", {table.datefield1}, {table.datefield2}, crMonday)

In your case,the formula will be

DateDiff("d",{OPCH.TaxDate},{OPOR.DocDueDate})

just paste this in the formula editor,check for error and save.

Hope this clarifies you!

[Former Member · Accepted solution]

Thanks for the formula. I had something similar. After studying your email I realized i could make a change to your formula

DateDiff("d",{OPCH.TaxDate},{OPOR.DocDueDate})

to

DateDiff("d",{OPCH.TaxDate},{@Promise Date}

Based on this I got the correct result.

The {@Promise Date} was what I originally used to determine the corect due date based on if the invoice was copied from a GR or a PO.

Here is my formula for {@Promise Date}

if{PCH1.BaseType}= 22 then {POR1.ShipDate}

else

if {PCH1.BaseType}= 20 then {POR1_1.ShipDate}

If you notice there is a tabble POR1 and POR1_1

Hopefully this will help someone else in the future.

Thanks for everybodies help.

Steve

[former_member193430 · Accepted solution]

Guys what is wrong i am doing here !

assume todays date is 5/28/2013

Datediff('d',{ZVBAK_OPN_ORDRS_ENHANCE.VBAK-VDATU},CurrentDate) <= 1 it shows me 5/27/2013, 5/28/2013, 5/29/2013. Instead i am looking to see the 5/27/2013, 5/28/2013.

Thanks. Toor.