Hello,
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 ?

Isabelle

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.

if you were using mysql, you could store as follows:

-- if you have only the year, store yyyy-00-00

-- if you have only the year and month, store yyyy-mm-00

-- if you have just the century, i'm not sure


and of course i have no idea about the Matisse database, i've never used it

i'm thinking that before you decide on a final storage mechanism, you should consider how you would write the query to return all objects from the mid 18th century