RE: Date to Julian date conversion

Informix stores a "date" column as an integer, representing the number of days since Dec 31, 1899. You can verify this behavior by running a script similar to the following through dbaccess: CREATE TABLE XX (field1 date); INSERT INTO XX VALUES ('01/17/2001'); -- Use any valid date ALTER TABLE XX MODIFY (field1 INTEGER); -- Convert date to integer SELECT * FROM XX; -- Display date as integer Result of the above SELECT statement is: field1 36907 -----Original Message----- From: Sinha, Niraj [mailto:niraj.sinha@BondBook.com] Sent: Monday, January 15, 2001 14:05 To: 'informix-list@iiug.org' Subject: Date to Julian date conversion Does any one know how to convert the DATE data type to JULIAN date ?? I know informix stores teh DATE as JULINA date internally ?? Thx, Niraj

Depending on the version you are using, it can be as simple as: SELECT field_as_date::int AS field_as_int FROM table ... or, for that matter, SELECT TRUNC(field_as_date) AS field_as_int FROM table However, if you don't want the Informix internal date, but rather the day-of-year (1-365), you could use: SELECT field_as_date - MDY(12,31,YEAR(field_as_date) - 1) AS day_of_year FROM table Hope that helps, RET -- +--------------------------------+---------------------------------+ ! RET Consulting Pty Ltd ! Richard Thomas ! ! Database Design, ! ph: 0413 00 1957 (Australia) ! ! Implementation and Consulting ! email: ret@cornerpub.com ! +--------------------------------+---------------------------------+ > From: "Bernstein, Rick" <rbernste@alarismed.com> > Organization: Mailing List Gateway > Reply-To: "Bernstein, Rick" <rbernste@alarismed.com> > Newsgroups: comp.databases.informix > Date: Wed, 17 Jan 2001 08:21:48 -0800 > Subject: RE: Date to Julian date conversion > > > Informix stores a "date" column as an integer, representing the number of > days since Dec 31, 1899. You can verify this behavior by running > a script similar to the following through dbaccess: > > CREATE TABLE XX (field1 date); > INSERT INTO XX VALUES ('01/17/2001'); -- Use any valid date > ALTER TABLE XX MODIFY (field1 INTEGER); -- Convert date to integer > SELECT * FROM XX; -- Display date as integer> > Result of the above SELECT statement is: > field1 > 36907 > > -----Original Message----- > From: Sinha, Niraj [mailto:niraj.sinha@BondBook.com] > Sent: Monday, January 15, 2001 14:05 > To: 'informix-list@iiug.org' > Subject: Date to Julian date conversion > > > Does any one know how to convert the DATE data type to JULIAN date ?? > I know informix stores teh DATE as JULINA date internally ?? > > Thx, > Niraj