Format field Date

Hi developers,

I explain… I got a base calendar with an “Annual Recurring” record for the day 01/05, the problem is that in table 7601 is not stored only 01/05, it’s stored 01/05/07 although this is a nonworking day for every year. The problem appears when I want to know if a day is nonworking, everything is working good but this:

varDate := ‘01/05/09’;
SETRANGE(Date, varDate);

This doesn’t filter the day, so I would be able to format both dates, something like…

SETRANGE(format(Date,0,’’), format(varDate,0,’’));

But FORMAT function returns String, therefore an error is displayed.
Any suggestion?
Thank u all

Instead of using FORMAT use EVALUATE.
EVALUATE will convert a string to date.

And even better:

SETRANGE(Date,DMY2DATE(1,5,2009));

and why not use:

SETRANGE(Date,01052009D);

Kriki and Josef,

I think I can’t use your functions cause what I need is to filter de table field. For example, the table field has the date value ‘01/07/07’, I want to return this field if filter is ‘01/07/08’, ‘01/07/15’, ‘01/07/99’, any year… so I think I need to “cut” the table field Date.

Did I explain properly?

If I understand well, you want to filter ONLY on day + month?

In this case I would create an integer field in which I would put the month + day in this format: MMDD (in your case : 0701). And then I would put this filter:

datTheDate := DMY2DATE(1,7,2007);

SETRANGE(“New Field”,DATE2DMY(datTheDate,2) * 100 + DATE2DMY(datTheDate,1));

TO fill up the field, you can use this code in the OnValidate-trigger of the date-field (in you use this) or some other place :

“New Field” := DATE2DMY(datTheDate,2) * 100 + DATE2DMY(datTheDate,1);

It seems a good idea those functions but I got an error message (“A field from a record variable was expected. For example: Record.Field”) in this line:

SETRANGE(DATE2DMY(Date,2), DATE2DMY(fecha,2));

Obviously I can’t “cut” the month of the field Date.
I’m afraid I still thing in SQL, where it would be so easy to query “Where format(Date,“mm/yyyy”) = format(fecha,“mm/yyyy”)” jajajaja

Any other advise?

You forgot to mention field name.

It’s SETRANGE(“Field”, DATE2DMY(Date,2), DATE2DMY(fecha,2)) and not SETRANGE(DATE2DMY(Date,2), DATE2DMY(fecha,2));

Thanks for posting Nuno but… I didn’t forgot the field name, it’s “Date” and that’s the real the problem, I need to format the field name to non-consider the year of this field. Look a few posts above

Thanks

¿?

  1. don’t use system-variables (like Date) as a fieldname. You can get into trouble with this.

  2. You need to create a new field that is an integer. In this you store the day and the month as per formula in my previous post. And then you need to filter on this new field like I wrote in my previous post.

Jajajaja, I think I haven’t been able to explain properly yet, but don’t worry, I’ll try it myself…

Date is neither a variable nor a datafield created for me, is a datafield from table 7601 (Base Calendar Change), own of NAV. I’m not sure if you refer that Date is a system-variable like TODAY or USERID, I’m developing in Spain and I can use Date as variable without problems, but as I said I’m not doing it.

Anyway, I think I found a solution, better lucky next time [:’(]

Thank u all for your patient.

Best regards