How to validate a date in TIBCO Data Virtualization?
Article ID: KB0075343
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 7.0 and higher |
Description
How to validate a date in TIBCO Data Virtualization?
Issue/Introduction
How to validate a date in TIBCO Data Virtualization?
Resolution
TDV does not have a date validation function. It is possible to write a validation procedure that uses a date-related function like TO_DATE() or uses custom logic to test the validity of a date. Below is an example procedure that takes a string as an input. The procedure returns a message stating whether the input string contains a valid date or not:
When a valid date '2020-12-31' is provided as the input, CheckDate returns:
Year: 2020-12-31
When an invalid date '2020-12-32' is provided as the input, CheckDate returns:
2020-12-32 is not valid A system exception has occurred. The string '2020-12-32' is not a properly formatted date or out of range. It must be formatted as follows: 'yyyy-MM-dd'. Acceptable range is from '0001-01-01' to '9999-12-31'.
PROCEDURE CheckDate (IN the_date VARCHAR, OUT validity VARCHAR) BEGIN declare cur CURSOR; declare temp1 VARCHAR(15); open cur for SELECT TO_DATE(the_date) from /services/databases/system/DUAL; fetch cur into temp1; set validity = 'Year: ' || temp1 ; EXCEPTION ELSE IF (LENGTH(CURRENT_EXCEPTION.MESSAGE) > 1 ) THEN set validity = the_date || ' is not valid ' || CURRENT_EXCEPTION.MESSAGE; END IF; END
When a valid date '2020-12-31' is provided as the input, CheckDate returns:
Year: 2020-12-31
When an invalid date '2020-12-32' is provided as the input, CheckDate returns:
2020-12-32 is not valid A system exception has occurred. The string '2020-12-32' is not a properly formatted date or out of range. It must be formatted as follows: 'yyyy-MM-dd'. Acceptable range is from '0001-01-01' to '9999-12-31'.
Feedback
Yes
No
Powered by
