RE: TIMESTAMP_FORMAT

[Stephen Landess <steve_landess@xxxxxxxxxxx>]

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