Re: Julian Date Conversion

}>From ilist@rmy.emory.edu Mon Jun 16 14:16:36 1997 }>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (950413.SGI.8.6.12/930416.SGI.AUTO) }> for <settler@dbintellect.com> id OAA04574; Mon, 16 Jun 1997 14:15:39 -0600 }>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id NAA15681 for informix-list-out; Mon, 16 Jun 1997 13:05:01 -0400 (EDT) }>Date: Mon, 16 Jun 1997 09:58:56 -0700 }>From: johnl@informix.com (Jonathan Leffler) }>Message-Id: <199706161658.JAA12695@anubis.informix.com> }>To: informix-list@rmy.emory.edu }>Subject: Re: Julian Date Conversion }>Sender: informix-list-owner@rmy.emory.edu }>Reply-To: johnl@informix.com (Jonathan Leffler) }>X-Informix-List-Id: <list.15000> }> }>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; }> }>Yours succinctly, }>Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }> }>PS: Jeff -- your outgoing email didn't have any domain name on your }>address, so it appeared to come from settler@rmy.emory.edu. I fixed this }>in the message below. }> }>}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; }>} }>}------------------------------------------------------------------- }>}Jeff Craig | dbINTELLECT Technologies, Inc. }>}Senior Consultant | Golden, CO }>} | (303) 275-6954 ext. 8008 }>} | email: settler@dbintellect.com }>} | fax: (303) 275-2134 }>}------------------------------------------------------------------- }>} }> Doesn't the procedure you've indicated merely transform the DATETIME data type to a DATE data type? 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. ------------------------------------------------------------------- Jeff Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------