Re: Julian Date Conversion

On Tue, 10 Jun 1990, Chris Kaeberlein wrote: > I'm in need of a function that will convert a datatype of DATETIME YEAR > TO DAY into a Julian date. Hrm. Try this: CREATE TABLE tablename . . . FRAGMENT BY EXPRESSION MOD(DATE(datetime_field) - DATE('1/1/1990'),365) => 0 AND MOD(DATE(datetime_field) - DATE('1/1/1990'),365) < 90 IN dbs1 MOD(DATE(datetime_field) - DATE('1/1/1990'),365) => 90 AND MOD(DATE(datetime_field) - DATE('1/1/1990'),365) < 180 IN dbs2 MOD(DATE(datetime_field) - DATE('1/1/1990'),365) => 180 AND MOD(DATE(datetime_field) - DATE('1/1/1990'),365) < 270 IN dbs3 MOD(DATE(datetime_field) - DATE('1/1/1990'),365) => 270 IN dbs4 Replace 1990 with the earliest year in the table, and it should be close. > I've searched all of the Informix literature > and CD documentation that I have at my disposal and haven't been able to > locate anything. Hey, at least you tried. > The reason I need this function is that I need to > fragment a database table by the DATETIME field, and hopefully fragment > the data evenly across 4 fragments. Why are you fragmenting this by DATETIME, may I ask? > However, I need to convert the > DATETIME field to a Julian value first in order to use the DATETIME > field in the fragmentation expression. Any help in this matter would be > much appreciated. Hrm. I'm not sure if what I have above would work for you, or even work, I haven't tried it to be honest. But unless I'm falling into some of the odd FRAGMENT BY EXPRESSION syntax blocks, it should work. The docs (ODS-AG 16-9) seem to talk about three different methods (hash, arbitrary and range) as if they're independant with their own syntax requirements, some of which don't seem to include MOD. But I think it should work. [Grin] If it does work, I'd appreciate it if you'd let me know! -Richard