| Author |
Message
|
| ramki |
 Posted: Mon Feb 25, 2002 7:19 am Post subject: |
 |
|
Apprentice
Joined: 25 Sep 2001
Posts: 28
|
Have any one of you done a conversion of CCYYDDD (Julian date) to MMDDCCYY (Gregorian date)and vice versa in MQSI? I have not seen a function for that. All and any help will be appreciated. Thanks
|
|
| Back to top |
|
 |
| meekings |
| Posted: Mon Feb 25, 2002 12:45 pm Post subject: |
|
|
Voyager
Joined: 28 Jun 2001
Posts: 86
Location: UK, South West
|
I don't think there's a straightforward way to do this. The long-winded way is to:
extract the year, determine if it's a leap year
extract the julian date
starting with a month counter at 1, progressively subtract 31, 28/29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31, incrementing the counter until the julian date would go negative
now you have the day, month and year
Very klunky in ESQL but bog-standard programming. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Feb 25, 2002 5:33 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
If your database provides a date function to convert from Julian date to Gregorian date, you could call that function using PASSTHRU in ESQL. If not, write a stored function which does this conversion for you and call it in ESQL.
Regards,
Kiran
|
|
| Back to top |
|
|
| tchagan |
| Posted: Tue Feb 26, 2002 7:47 am Post subject: |
|
|
Apprentice
Joined: 10 Feb 2002
Posts: 31
|
I've created standard message sub-flows for this sort of thing. MQSI has no date conversion functions which has been a major complaint of mine to them since I started using the product over a year ago.
Hard coding is the only way to go in this instance, using a database is an idea but you are then building in potential failures to your message flow if the db is down.
The best idea would be for IBM to build in some formatting routines like any decent language would have.
cheers
Terry  |
|
| Back to top |
|
|
| Miriam Kaestner |
| Posted: Tue Feb 26, 2002 8:18 am Post subject: |
|
|
Centurion
Joined: 26 Jun 2001
Posts: 103
Location: IBM IT Education Services, Germany
|
In WMQI 2.1, there is support for custom date/time formats in the MRM.
The Format property of DATETIME elements accepts format strings in ICU format. |
|
| Back to top |
|
|
| ramki |
| Posted: Wed Mar 06, 2002 12:38 pm Post subject: |
|
|
Apprentice
Joined: 25 Sep 2001
Posts: 28
|
Mathematicians and programmers have naturally interested themselves in mathematical and computational algorithms to convert between Julian day numbers and Gregorian dates. The following conversion algorithm is due to Henry F. Fliegel and Thomas C. Van Flandern:
The Julian day (jd) is computed from Gregorian day, month and year (d, m, y) as follows:
jd = ( 1461 * ( y + 4800 + ( m - 14 ) / 12 ) ) / 4 +
( 367 * ( m - 2 - 12 * ( ( m - 14 ) / 12 ) ) ) / 12 -
( 3 * ( ( y + 4900 + ( m - 14 ) / 12 ) / 100 ) ) / 4 +
d - 32075
Converting from the Julian day number to the Gregorian date is performed thus:
l = jd + 68569
n = ( 4 * l ) / 146097
l = l - ( 146097 * n + 3 ) / 4
i = ( 4000 * ( l + 1 ) ) / 1461001
l = l - ( 1461 * i ) / 4 + 31
j = ( 80 * l ) / 2447
d = l - ( 2447 * j ) / 80
l = j / 11
m = j + 2 - ( 12 * l )
y = 100 * ( n - 49 ) + i + l
Days are integer values in the range 1-31, months are integers in the range 1-12, and years are positive or negative integers. Division is to be understood as in integer arithmetic, with remainders discarded.
more info on
http://serendipity.magnet.ch/hermetic/cal_stud/jdn.htm#comp |
|
| Back to top |
|
|
| timjohnarm |
| Posted: Mon Feb 06, 2006 1:34 pm Post subject: |
|
|
Apprentice
Joined: 28 Sep 2004
Posts: 40
Location: Melbourne Australia
|
Couln't get the formula to work. Following seems to work. Have also posted this in "noofdays - julian date" where this solution was suggested by Eddie Atherton.
CREATE FUNCTION CharToJulianDate(DD CHARACTER, MM CHARACTER, CCYY CHARACTER) RETURNS CHARACTER
BEGIN
DECLARE CCYYMMDD DATE;
DECLARE CCYY0101 DATE;
DECLARE DaysThisYear INTEGER;
DECLARE JulianResult CHARACTER;
DECLARE Pos INTEGER;
SET CCYYMMDD = CAST((CCYY || '-' || MM || '-' || DD) AS DATE);
SET CCYY0101 = CAST((CCYY || '-01-01') AS DATE);
SET DaysThisYear = CAST((CCYYMMDD - CCYY0101) DAY AS INTEGER) + 1;
SET JulianResult = '000' || CAST(DaysThisYear AS CHARACTER);
SET Pos = LENGTH(JulianResult) - 2;
SET JulianResult = CCYY || SUBSTRING(JulianResult FROM Pos FOR 3);
RETURN JulianResult;
END; |
|
| Back to top |
|
|
| EddieA |
| Posted: Mon Feb 06, 2006 1:45 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Even better. This one's 4 years old. 
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| TonyD |
| Posted: Mon May 22, 2006 8:45 pm Post subject: |
|
|
Knight
Joined: 15 May 2001
Posts: 540
Location: New Zealand
|
| I need to convert from Julian Day to Gregorian date .... tried the formula shown above but for 23 May 2006 I get 2453981 when it should be 2453879. Has anyone written ESQL to do this....or does it need Java? |
|
| Back to top |
|
|
| mgk |
| Posted: Tue May 23, 2006 12:46 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
The following should help, note this is V6 only, and is not tested code.
| Code: |
--from Julian to Gregorian
DECLARE julian CHARACTER '2006040'; --- 040th day in 2006
DECLARE julianpattern CHARACTER 'yyyyDDD';
DECLARE intermediate DATE;
DECLARE gregorianpattern CHARACTER 'MMddyyyy';
DECLARE gregorian CHARACTER;
SET intermediate = CAST(julian AS DATE FORMAT julianpattern );
SET gregorian = CAST(intermediate AS CHARACTER FORMAT gregorianpattern);
-- gregorian is now '02092006'
-- from gregorian to julian
SET intermediate = CAST(gregorian AS DATE FORMAT gregorianpattern);
SET julian = CAST(intermediate AS CHARACTER FORMAT julianpattern); |
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue May 23, 2006 3:46 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looking at V6 you could also use the INTERVAL capability to determine the number of days from Jan 1st ....
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| TonyD |
| Posted: Tue May 23, 2006 12:53 pm Post subject: |
|
|
Knight
Joined: 15 May 2001
Posts: 540
Location: New Zealand
|
Thanks for responses....I am a bit puzzled by the reference to Julian Date as 'yyyyDDD'. The conversion I need is from a Julian day, actually day/time, in a format such as '2453879.36425' as per the following definition:
| Quote: |
The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero.
|
The involved calculation above gets close (within 100 days) but not close enough as I need to be accurate to the minute. |
|
| Back to top |
|
|
| EddieA |
| Posted: Tue May 23, 2006 2:33 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Pick a date, in the recent past, wher you know the correct Julian day. Subtract the Juilan day from your input Julian Day. That will give you a DAY INTERVAL from your "known date". Use the built-in functions to add that Interval to your "known date".
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
|
|
