Re: Julian Date Conversion
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
}-------------------------------------------------------------------
}