Unanswered: Date formats...How do i take all into consideration ??
I am setting up a Matisse database to contain information about art objects. One of the features is the date of an object. the problem is that a date for an art object can be in a number of formats, namely dd/mm/yyyy, mm/yyyy, yyyy, or century (early, mid, late). all these formats also need to be considered as either before the common era (aka BC) or the common era (aka AD). To make matters worse, I should be able to search by date... Any suggestions to codify these dates ?
In pretty much all databases these days, date inputs are accepted in a variety of formats, using the system's "locale" settings to provide a cue.
Use a "Date/Time" field type and let the DBMS do the work for you.
The date-value is stored in an entirely-separate format: normally, a number. The whole-number portion is the number of days elapsed since an arbitrary but known date (called the "epoch"). The fraction represents the time-of-day. (You never see it as a number, because the value is always translated into a human format of your choice before you see it.)
And in spite of the "Y2K flap," this representation for date/time values has been used pretty consistently since the 1960's. It can easily represent dates over many thousands of years, including BC and AD.