Re: Julian Date Conversion
>>From ilist@rmy.emory.edu Tue Jun 10 21:24:26 1997
>>Received: from mail.netcom.com by hobbes.dbintellect.com via ESMTP (950413.SGI.8.6.12/930416.SGI.AUTO)
>> for <settler@dbintellect.com> id VAA07533; Tue, 10 Jun 1997 21:24:16 -0600
>>Received: from rmy.rmy.emory.edu (rmy.rmy.emory.edu [170.140.97.4])
>> by mail.netcom.com (8.8.5-r-beta/8.8.5/(NETCOM v1.01)) with ESMTP id OAA11178
>> for <settler@dbintellect.com>; Tue, 10 Jun 1997 14:32:42 -0700 (PDT)
>>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id OAA03570 for informix-list-out; Tue, 10 Jun 1997 14:55:01 -0400 (EDT)
>>From: Chris Kaeberlein <chrisk@atlanta.com>
>>Message-Id: <339DC66B.24E3@atlanta.com>
>>Subject: Julian Date Conversion
>>Date: Tue, 10 Jun 1997 14:26:03 -0700
>>Reply-To: chrisk@atlanta.com
>>Organization: Greenbrier and Russel, Inc.
>>Sender: informix-list-owner@rmy.emory.edu
>>To: informix-list@rmy.emory.edu
>>X-Informix-List-Id: <news.39000>
>>
>>Hello, fellow, Informix-ites.
>>
>>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.
>>
>>TIA,
>>Chris
>>
>>--
>>Chris Kaeberlein
>>Greenbrier and Russel, Inc.
>>ckaeberlein@gr.com
>>
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
-------------------------------------------------------------------