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)


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Gad
Miron
Sent: Mittwoch, 1. November 2017 15:45
To: midrange-l@xxxxxxxxxxxx
Subject: SQL Valid Date

Hello guys

need help with the following:

I'm converting a numeric YYYYMMDD date to a true date with select EVNTDATE,
date(timestamp_format(CHAR(EVNTDATE) , 'YYYYMMDD')) from some_file It works
fine most of the time but it fails when the numeric EVNTDATE contains
invalid date like 19910229 (Feb. 29 1991) or 20001302 (Mama told me to avoid
month 13th at all cost)

Is there a SQL function to validate date? (like SQL Server's ISDATE)

TIA
Gad
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD