Birgitta wrote:

The query fails, because the calculation is converted into a Integer,
so 0002021123 does not match the format YYYYDDD.
... but I'm still not clear why you want to use in any was
Timestamp_Format/TO_DATE to convert a numeric value with a date
in the format CYYDDD into a real date!
Date will do the job much easiser.

The following query converts several numeric dates in the format CYYDDD into a real date,
using the DATE and TIMESTAMP_FORMAT Functions

With x (YourCYYDDD) as (Values(Cast(121123 as Dec(7, 0))),
(Cast(99364 as Dec(7, 0))),
(Cast(107234 as Dec(7, 0)))) Select YourCYYDDD,
Date(Right(Digits(1900000 + YourCYYDDD), 7)) "With Date",
Date(Timestamp_Format(Right(Digits(1900000 + YourCYYDDD), 7),
'YYYYDDD')) "With Timestamp_Format"
from x;

Birgitta -

Thanks for the information.

My goal was to find a simpler way to do the date conversion,
and Eric Lehti's suggestion to use Howard Arnr's
SQL UDF seems a good way to go:

http://www.sqlthing.com/Resources/JDDCONV.html

So, I created this UDF based on Howard's example:

CREATE FUNCTION QGPL/JDEDATE
(JDEDATE DECIMAL(6,0))
RETURNS DATE
LANGUAGE SQL
SET OPTION DATFMT=*ISO
BEGIN
DECLARE F_OUTPUT DATE ;
DECLARE F_TEST INTEGER ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ;
SET F_TEST = 0;
SET F_OUTPUT = DATE(DIGITS(DECIMAL(JDEDATE+1900000,7,0)));
IF F_TEST = 0 THEN RETURN F_OUTPUT ;
ELSE RETURN NULL;
END IF;
END

Best Regards,
Steve Landess
512-289-0387

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.