Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Unanswered: Date formats...How do i take all into consideration ??

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Thumbs up

    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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •