x
select ISDATE('20170213') from dummy;
select ISDATE('JUNK') from dummy;
How to do this is HANA SQL
.
Advertisement
Answer
Ok this seems to work.
select DATS_IS_VALID('20170213') from dummy; -- 1 -- expects YYYYMMDD only
select DATS_IS_VALID('2017-0213') from dummy; -- 0
select DATS_IS_VALID('2017-02-13') from dummy; -- 0
select DATS_IS_VALID('20170233') from dummy; -- 0
select DATS_IS_VALID('21-02-2017') from dummy; -- 0
select DATS_IS_VALID('02212017') from dummy; -- 0
see also tstmp_is_valid() for timestamps. see also here
Alternatively – use a UDF – takes only YYYYMMDD and YYYY-MM-DD
--DROP FUNCTION ISDATE;
CREATE FUNCTION ISDATE (inputval NVARCHAR(100))
-- expects format YYYYMMDD
RETURNS RETURNVAL BOOLEAN
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE DT date;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RETURNVAL := false;
END;
select CAST(:inputval AS DATE) into DT from dummy;
RETURNVAL := true;
END;
select ISDATE('20170213') from dummy; --true
select ISDATE('2017-02-13') from dummy; --true
select ISDATE('2017-FEB-13') from dummy; --false
select ISDATE('13-12-2017') from dummy; --false
select ISDATE('abc') from dummy; --false
select ISDATE('13-Dec-2017') from dummy; --false
select ISDATE('20170232') from dummy; --false
Here is ‘one shot’ ‘to date’ that handles bad data – returns NULL if not valid , make sure that all your dates match the format string or it will convert to null.
CREATE FUNCTION TO_DATE_V2 (inputval NVARCHAR(100), format_str NVARCHAR(20))
RETURNS RETURNVAL DATE
LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE DT date;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
RETURNVAL := NULL;
END;
select TO_DATE(:inputval, :format_str) into DT from dummy;
RETURNVAL := :DT;
END;
select TO_DATE_V2('20-04/2017', 'DD/MM/YYYY') from dummy; -- null
select TO_DATE_V2('20/04/2017', 'DD/MM/YYYY') from dummy; -- 2017-04-20
select TO_DATE_V2('JUNK', 'DD/MM/YYYY') from dummy; -- null