DATE vs. DATETIME YEAR TO DAY

Thanks, Cathy... -> ->I have a piece of attribute called "destruction date" and it's computed from ->two smallint attributes (julian date and retention period). The application ->(ESQL/C) needs to find all the records by a given year or between dates, for ->example between 01/01/1994 and 12/31/1994, after the system runs for a period ->of time. -> ->First I chose "DATE" for "destruction date" since type DATE, by Informix, is ->a 4-bytes integer. Since the table contains "destruction date" is going to be ->very large and the DBA will have an index on "destruction date". The DBA ->thinks the data type ->should be "DATETIME YEAR TO DAY" since "destruction date" will be stored in a ->string as "YYYY/MM/DD" and SELECT queries on a given year or between two dates ->will perform better via the index (although the application has to do conversion491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205 ->in order to store an integer date into a "DATETIME YEAR TO DAY" column). > ->I would like to know which data type (DATE or DATETIME YEAR TO DAY) makes ->better performance for our circumtance. -> ->Thanks in advance for any input ! -> ->Diana Li Neither data nor datetime stores a string. They convert to a number. Date types are less complex and the conversion is simpler (number of days since 12/31/1899, I believe), therefore I believe your performance would be best using date and your code will be less complex. The performance difference between the two types will probably not be great though. Regards, - Cathy ------------------------------------------------------------------------------ -- Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (970) 491-1294 Colorado State University Veterinary Teaching Hospital Fax: (970) 491-1205