RE: TIMESTAMP_FORMAT

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