Re: Julian Date Conversion

I've reorganized the order of the information in this message to get the new material at the top. }Date: Wed, 18 Jun 1997 08:11:28 -0600 }From: settler@informixs-bh.informix.com (Jeff Craig) [...Grrr...malformed mail headers...] }Doesn't the procedure you've indicated merely transform the DATETIME }data type to a DATE data type? Actually, it converts a DATE type (or a DATETIME implicitly converted to a DATE type) into the number of days since 31st December 1899. }It is still not in Julian format, unless I am missing something. Julian }format is either YYYYDDD, where YYYY is the century plus year, and DDD is }the day of the year, OR Julian format is a number representing the number }of days that have passed since a fixed date in time. No, it certainly doesn't put it into the format you are talking about. I thought that Julian dates were simply the number of days past some reference date; my code was using the 31st December 1899 reference date for lack of a defined alternative. If Julian Dates are formatted as you describe, then my code is not even attempting to solve the same problem, and my comments about your code are completely off the mark. I apologize for any misunderstanding. Yours, Jonathan Leffler (johnl@informix.com) #include <what-is-a-julian-date.h> }From: johnl@informix.com (Jonathan Leffler) }Date: Mon, 16 Jun 1997 09:58:56 -0700 } }Jeff's routine probably works, but seems awfully long-winded. }This works, with the conversion being done by the engine... } }CREATE PROCEDURE to_julian(d DATE) RETURNING INTEGER; } RETURN d; }END PROCEDURE; } }}Date: Sun, 15 Jun 1997 12:25:53 -0600 }}From: settler@dbintellect.com (Jeff Craig) }}X-Informix-List-Id: <list.14981> }} }}>>From: Chris Kaeberlein <chrisk@atlanta.com> }}>>Date: Tue, 10 Jun 1997 14:26:03 -0700 }}>>X-Informix-List-Id: <news.39000> }}>> }}>>I'm in need of a function that will convert a datatype of DATETIME YEAR }}>>TO DAY into a Julian date. 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. 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. 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. }} }}Here is a Stored Procedure script that works for DATE, you could }}modify it for DATETIME. It converts a DATE value to an integer }}that represents the number of days that have passed since }}12/31/1899, the Informix base date used for all date calculations. }}This script includes no express or implied warranties. }} }}-- ################################################################ }}-- # }}-- # $Id$ }}-- # }}-- # SCRIPT: DateToChar.sql }}-- # }}-- # FUNC: Converts a DATE value to a Character string. }}-- # }}-- # DESC: - }}-- # }}-- # INPUTS: - DATE value }}-- # - CHAR(1) value for Conversion Type, where }}-- # 'J'= Julian days }}-- # 'Y'= YYYYMMDD }}-- # }}-- # OUTPUTS: - VARCHAR(8) value with passed date in selected }}-- # format. }}-- # }}-- # NOTES: - If you do not specify the conversion type, it will }}-- # default to a Julian date conversion. }}-- # }}-- # SAMPLE: - SELECT DATETOCHAR(OPEN_DATE,'J') }}-- # FROM PRPACT_PROP_ACCT }}-- # WHERE ACCT_ID = 17; }}-- # }}-- # CHANGE LOG: }}-- # }}-- # $Log$ }}-- # }}-- ################################################################ }} }}DROP PROCEDURE DateToChar; }}CREATE PROCEDURE DateToChar (indate DATE DEFAULT NULL }} ,inchar CHAR(1) DEFAULT NULL }} ) }} RETURNING VARCHAR(8); }}-- ############################ }}-- ## INITIALIZATION SECTION ## }}-- ############################ }} }} DEFINE outchar VARCHAR(8); }} DEFINE workdate DATE; }} DEFINE numdays INTEGER; }} DEFINE fill1 VARCHAR(2); }} DEFINE fill2 VARCHAR(2); }} }}-- # Set leading zero on day and month values }} IF MONTH(indate) < 10 THEN }} LET fill1 = '0' || MONTH(indate); }} ELSE }} LET fill1 = MONTH(indate); }} END IF; }} }} IF DAY(indate) < 10 THEN }} LET fill2 = '0' || DAY(indate); }} ELSE }} LET fill2 = DAY(indate); }} END IF; }} }}-- ############################ }}-- ## MAINLINE ## }}-- ############################ }} }}-- # Check for null input date, return null if found }} IF indate IS NULL THEN }} LET outchar = NULL; }}-- # Convert to YYYYMMDD format }} ELIF inchar = 'Y' THEN }} LET outchar = YEAR(indate) || fill1 || fill2; }}-- # Convert to "Julian" format, which in this case, is the }}-- # number of days since Informix initialization date }} ELSE }} LET workdate = MDY(12,31,1899); }} LET numdays = indate - workdate; }} LET outchar = numdays; }} END IF; }} }}-- ############################ }}-- ## FINALIZATION ## }}-- ############################ }} }} RETURN outchar; }} }}END PROCEDURE; }} }}UPDATE STATISTICS FOR PROCEDURE DATETOCHAR;