Date formats in Base date controls [solved]

[flabdablet]

When I put a Date control in a Base form, I can find no way to make the date format look like

31 Dec 2007

which is the format I generally use elsewhere. I've set the format of the underlying table field to D MMM YYYY, so I get the format I want in table views, but I can't get the same format for date controls on forms.

The "Date format" property for a Date control has a dropdown list of formats, but D MMM YYYY isn't on it and I can't see any obvious way to add it. What am I missing?

[CFRandom]

I have a similar problem. I want to use a date format that is just mo/yr. This is a format option in the Table, but not in the Form view.

Am I missing something, or is there a bug here?

Roger

[flabdablet]

OK, it seems the solution is "don't use a Date control".

If I replace it with a Formatted Field control, I can specify my own formatting string and everything else appears to work the way I'd expect.

If anybody knows of subtle benefits of using a Date control that I'm missing out on by using Formatted Field instead, I'd appreciate hearing about them.

Thanks for taking the time to consider my question.

[TorolfJ]

Use a text box. If you have set the format at the table level it will display that way in the text box.

[flabdablet]

Yes! That's better still. I was a little annoyed by having to specify the date format in two places; now I'm not! Thanks!

[MSPhobe]

For some jobs, the answers above work... but I suspect that they will not work if....

1) You want to do "dates arithmetic" (e.g. ask for number of days between earliest and latest record for, say, "customer x"

or

2) You want to sort the data by the data in the date field.

For the latter requirement (sorting), you can overcome the limitation of using text for dates by showing the dates as follows. I'll start with the simple case, where you only need year and month....

07-1 for Jan 07
07-2
07-3.....
07-a for October
07-b
07-c (December)

It feels odd at first, but soon becomes familiar and easy. You can leave out the hyphens if you want to save yourself lots of typing. (E.g. 07c for December '07)

If you must have the day as well, it will work if you put leading 0's in front of days 1-9, e.g.....

07-a-01 (1st October 07)
07-a-02...

07-a0-08
07-a-09
07-a-10
07-a-11.... (11 Oct 07)

The above WILL work just fine, with little hassle, just a little getting used to the odd format. However, it SHOULD be possible to get ooBase to "behave", and do things "the human way"... if you want to do the fighting!

[MSPhobe]

For some jobs, the answers above work... but I suspect that they will not work if....

1) You want to do "dates arithmetic" (e.g. ask for number of days between earliest and latest record for, say, "customer x"

or

2) You want to sort the data by the data in the date field.

For the latter requirement (sorting), you can overcome the limitation of using text for dates by showing the dates as follows. I'll start with the simple case, where you only need year and month....

07-1 for Jan 07
07-2
07-3.....
07-a for October
07-b
07-c (December)

It feels odd at first, but soon becomes familiar and easy. You can leave out the hyphens if you want to save yourself lots of typing. (E.g. 07c for December '07)

If you must have the day as well, it will work if you put leading 0's in front of days 1-9, e.g.....

07-a-01 (1st October 07)
07-a-02...

07-a0-08
07-a-09
07-a-10
07-a-11.... (11 Oct 07)

The above WILL work just fine, with little hassle, just a little getting used to the odd format. However, it SHOULD be possible to get ooBase to "behave", and do things "the human way"... if you want to do the fighting!

[flabdablet]

Just to clear this up: there's still a Date field in the underlying table. It's just that to expose that date in a control on a form, and get it formatted the way I want, I have to change the Date control created by the Form Wizard into a Text Box control. Only the control type changes; the data source doesn't. With a Text Box control for a Date data source, the date format I specify in the original table then gets used in the control automatically (i.e. I don't have to choose an explicit date format in the control's properties).

I find I can enter anything even vaguely date-like into such a control, and Base will correctly convert it to a date, store it as such in the underlying table, and then reformat it for display according to the format chosen for the table field. There should be no problem doing date arithmetic, sorting etc. as the underlying data really is a Date; it's not stored as text.

As far as I can tell, the only downside of doing it this way is that I miss out on the Date control's cute little date-selector mini-calendar. But if I can't have both the mini-calendar and the date display format I prefer, I'll happily lose the mini-calendar.

[flabdablet]

An afterthought: if anybody really truly does need to store dates as text, and needs them to sort properly, there's no need to invent Yet Another Date Format that nobody will understand without explanation. Just use ISO 8601 date format.