DATE parameter problem in formula with dates from DB 2

[Hueby]

hello all,

using CR 10, I have a DATE parameter (@enddate) where user's enter a date. I then want to use it at the end of this formula:

Round((datediff("d",minimum({ET_REQ_HISTORY_HEADER_MC.Requisition_Date},{ET_REQ_HISTORY_DETAIL_MC.Equipment_Code}), {?@enddate}) * .7143),0)

Though, it yells at me saying the parameter is not a date. Do I need to convert it somehow then? ..or is the problem from the other date tables in my SQL DB?

 

[roberthagan]

Since the validity checker is asking for a date, that implies that the date fields in your table are of date type.

Not to belabor the obvious, but did you define the param as a date when you created it? The default is string. I sometimes forget to set it to date. If for some reason you didn't want it initialized as a date, you could convert to it one by doing DATE({@enddate}), though I think it would have to be in "2006,9,13" format.

I've only used up to CR.NET - I'm not sure what the "?" at the beginning is. The standard param format is/was {@enddate}. The ? might turn it to an unknown type.

 

[synapsevampire]

If you specified a date type at creation time, then it's a date type, so the problem might be with the other date data types, right click the date field and select browse data to learn it's type and convert them in a formula if required.

If you want to test for a date, use the ISDATE() function.

-k

 

[Hueby]

Thanks everyone, I have doubled checked, and the parameters are DATE. When browsing the other date field, it comes up as a DATETIME (MM/DD/YYYY 00:00:00). So it looks like the easiest would be to convert the other date fields then?

I have this code working, so I assume the CURRENTDATE function is a DATETIME then?

Round((datediff("d",minimum({ET_REQ_HISTORY_HEADER_MC.Requisition_Date},{ET_REQ_HISTORY_DETAIL_MC.Equipment_Code}), CurrentDate) * .7143),0)

 

[lbass]

I just checked your formula using a datetime field for the requisition date and a date parameter for {?enddate} and it worked fine as is. Y

You are showing your date parameter as {?@enddate}--a parameter would never show up like this unless you named it "@enddate"--so I'm wondering whether you are using a formula {@enddate} in your formula by mistake, instead of the actual parameter?

Currentdate is a date. Currentdatetime is a datetime.

-LB

 

[synapsevampire]

Has nothing to do with being a date vs. a datetime.

You show {@enddate} and then you show [?@enddate} in the formula, so I'm concerned about what you're doing. Why is there a @ in it? That indicates a formula, not a parm.

Anyway, just simplify your formula:

Round(
(
{?enddate}-cdate(
minimum({ET_REQ_HISTORY_HEADER_MC.Requisition_Date},{ET_REQ_HISTORY_DETAIL_MC.Equipment_Code}
)
)
* .7143),0)

-k

 

[Hueby]

Thank you both, I got it now... it was a problem with the parameter naming. Your extra eyes helped out a bunch!