0
select ISDATE('20170213') from dummy;

select ISDATE('JUNK') from dummy;

How to do this is HANA SQL.

Ed Bangga
  • 12,879
  • 4
  • 16
  • 30
ozmike
  • 2,738
  • 1
  • 33
  • 40

1 Answers1

4

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
ozmike
  • 2,738
  • 1
  • 33
  • 40
  • 1
    This is very similar to the `IS_NUMERIC()` topic, both in the problem and the solution approaches. I've written more extensively on that [here] (https://lbreddemann.org/whats-your-number/). The main issue with trying the conversion and catching any errors is performance. Using the UDF adds runtime and catching errors is even worse. For better performance using regex-functions is often faster (though really ugly in the code). – Lars Br. Oct 10 '19 at 07:41
  • Regexp would be faster but dates have to cater for leap years etc. I'm sure there is a regexp somewhere that would do it. But each DB type will have different ranges ie. is 12-DEC-1612 a valid date? Only the DB knows this regexp can't. This is especially true if importing from third party sources you don't know what you get. – ozmike Jun 24 '20 at 03:56
  • is correct: there is a lot more to data validation than just looking at the string format. As so often it comes down to be clear about what should be achieved and what won't be part of the solution. Commonly, it would also be important to keep track of mismatching data to allow for correction later on. – Lars Br. Jun 24 '20 at 04:05