RE: SQL Valid Date (Birgitta Hauser)

[Gad Miron <gadmiron@xxxxxxxxx>]

Thanks Briggita

I'll test first thing.
Gad


> 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)