RE: SQL Valid Date (Birgitta Hauser)

message: 4
date: Wed, 1 Nov 2017 17:47:35 +0100
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: SQL Valid Date

Write your own function:
The following function will check whether the numeric date includes a valid
date.
For valid dates 1 is returned, for invalid dates 0 is returned
CREATE OR REPLACE FUNCTION YourSchema.CHKDATE (DATENUM DECIMAL(8, 0) )
RETURNS INTEGER
LANGUAGE SQL
SPECIFIC YOURSCHEMA/CHKDATE
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION DbgView=*Source
BEGIN
DECLARE VDATE DATE ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN 0 ;
SET VDATE = CAST ( DIGITS ( DATENUM ) CONCAT '000000' AS DATE ) ;
RETURN 1 ;
END ;

The following function will convert numeric dates into real dates.
For invalid numeric dates the 0001-01-01 is returned.
CREATE OR REPLACE FUNCTION YourSchema.NUM2DATE (DATENUM DECIMAL(8, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC YOURSCHEMA/NUM2DATE
DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION DBGView=*Source
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN DATE ( '0001-01-01'
) ;
RETURN CAST ( DIGITS ( DATENUM ) CONCAT '000000' AS DATE ) ;
END ;

Mit freundlichen Gr??en / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)