Date Diff formula returns message "The number of days is out of range" 2

[NewToThis2]

CV9 - I am trying to find the number of days from the effective date field to current. When I put the formula below into the report, I keep getting this message: "The number of days is out of range".

CurrentDate - {PA340___ALL.LogEffectDate}

Does anyone have any ideas on how I might fix this. The date field is formatted 20050105. I don't know if that makes a difference or not.

Thanks!

 

[kskid]

Use

DateDiff("d",CurrentDate,{PA340___ALL.LogEffectDate})

Cheers,
-LW

 

[TheBlondOne]

have you tried using datediff? i.e

datediff("d",{Field1},currentdate})

 

[kskid]

Ooops. Gave you the Wrong sequence

DateDiff("d",{PA340___ALL.LogEffectDate},CurrentDate)

Cheers,
-LW

 

[NewToThis2]

Thanks so much! When I use the datediff formula, the editor hilights the field portion of the formula {PA340___ALL.LogEffectDate} and says "A date is required here".

My source document is a csv file - does that make a difference?

 

[TheBlondOne]

What you might need to do is use the dtstodate function and place your field inside that. You may also be able to use the Cdate function. See examples below

i.e

datediff("d",dtstodate({field}),currentdate)

datediff("d",cdate({field}),currentdate}

HTH.

 

[NewToThis2]

Again, thanks so much for your help! I'm still not there yet though. The csv file is bringing in the date 20050105 from the database as a general field instead of a date field. I know this is not an idea situation but I'm trying to work with what I have. I do appreciate the ideas.

The results of the two formulas above:

----------------------------------
This one has no errors, but when I put it in the report, it brings back every count as zero.

datediff("d",dtstodate({PA340___ALL.LogEffectDate}),currentdate)

-----------------------------------
In the editor, this one says no errors found and then says "Bad date format string"

datediff("d",cdate({PA340___ALL.LogEffectDate}),currentdate)

 

[TheBlondOne]

Try creating a new formula which goes something like this;

whileprintingrecords;
stringvar one;
stringvar two;
stringvar three;
stringvar display;

one:= left({field},4);
two:= mid({field},4,2);
three:=right({field},2);

display:= one + "/" + two + "/" + three;

Then when you have done this just place this onto you report and have a look at it. The date should come back like this 2005/01/05

Then if you put this formula into the datediff formula i.e

datediff("d",dtstodate({@Formula}),currentdate) and see how that looks.

HTH

 

[kskid]

Or this

//@effdate
numbervar yr := tonumber(left({PA340___ALL.LogEffectDate}),4);
numbervar mo := tonumber(mid({PA340___ALL.LogEffectDate}),5,2);
numbervar dy := tonumber(right({PA340___ALL.LogEffectDate}),2)

date(yr,mo,dy)

datediff("d",{@effdate},CurrentDate)

Cheers,
-LW

 

[NewToThis2]

Excellent! That gave me what I want. I can always count on this forum - you guys are great!

Thanks so much everyone!!!!!