Re: dates B.C.

Ladies and Gentlemen, I've seen various speculations on what might or might not be possible in terms of handling dates BC, and I've sent various private messages to various people pointing out the error of their ways. However, since the discussion is ongoing, it seems relevant to say RTFM to everybody. And although it was jack's message that triggered this response, jack is by no means the only person who's forgotten what manuals are for. The FM in question is the Informix Guide to SQL: Reference, and the chapter on data types (chapter 3 in the 7.10 edition). There, it says, on p3-9, 'In this example, mm is the month (1-12), dd is the day of the month (1-31), and yyyy is the year (0001-9999)'. This has been documented in pretty much the same place in every version of the manuals I can lay my hands on. Similarly, on p3-10, under DATETIME, it says 'YEAR - A year numbered from 1 to 9999 (AD)'. Consequently, you cannot represent dates BC in Informix using the built in DATE and DATETIME types. Although the original question expressly ruled out a flag, that is the only way of handling them, short of using a CHAR(13) type with a CHECK constraint such as: (datecol MATCHES "[0-9][0-9][0-9][0-9]-[01][0-9]-[03][0-9] [AB][CD]") Don't forget, when doing calculations, that there was no year 0; 1 BC was follows by 1 AD. Also, don't forget that depending on which country you are in, the date sequence has a gap in it representing the switch from the Julian calendar to the Gregorian calendar. In the UK and the colonies (including the USA at the time), the switch occurred in September 1752; in most of continental Europe, it happened in 1584; in Russia, not until after 1917 (which is why the October Revolution happened in November!). And the switch to the Julian calendar caused problems in the early years AD (or was it just BC? I've forgotten.) So any date calculations prior to 1752 are dubious -- Informix does not take into account these calendrical oddities. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: Jack Parker <jparker@hpbs3645.boi.hp.com> }Date: Wed, 13 Mar 96 17:08:57 MST }X-Informix-List-Id: <list.9038> } }> In article <4hv3t1$o2r@accursio.comune.bologna.it> }> ret2519@iperbole.bologna.it "Giorgio Bignozzi" writes: }> }> > How can I use a date value to store a date Before Christ ? }> > Please don't suggest to use a flag ! }> }> I havn't tried this myself but as Informix stores dates as some kind of }> integer value I would suspect this is NOT a problem so long as your }> DBDATE is set to 'DMY4' (I'm in the UK!) not 'DMY2'. I would }> test this with a basic PERFORM screen. 31/Dec/1899 (or is it }> 01/Jan/1900?) are stored as zero. Dates before this are stored as }> negative numbers, dates after as positive ones - hence the ability }> do do arithmetic on dates. }> }> Hope this helps! } }Ok, but how do you enter them? -03/15/0044 (sorry Julius) doesn't go in on }a sperform form. I've tried a number of combinations of a negative sign and }any of those fields. All choke - invalid date. } }Ok, so: } }create table junk (dtfld DATE); } }insert into junk values ("01/01/0001"); } }select * from junk; } } dtfld } } 01/01/0001 -- so far so good. } }update junk set dtfld = dtfld - 1 units day; } } 519: Cannot update column to illegal value. } }(I also tried this with - 44 units year - to get over any conceptual problems } of year '0' - same result). } }I get more and more curious. based on this evidence I would venture to say }that Informix does not support BC dates. Still - I've been wrong once or }twice before.....