CODEWITHSUNDEEP
sql-serverdatenullinformix
JOINtheFun
JOINtheFun

Reputation: 27

Bad Dates: SQL Server Offers ISDATE(), But Informix Does Not. Is There an Equal Workaround?

I am dealing with legacy Informix data apparently never validated properly upon input.

This means that a DATE field could contain

The following works nicely in SQL Server:

SELECT
    COUNT(1) AS [Grand Total Count of Bad-Date Records],
    COUNT(GOOFYDATE) AS [Count of Just NON-NULL Bad-Date Records],
    SUM(IIF(GOOFYDATE IS NULL,1,0)) AS [Count of Just NULL Bad-Date Records]
FROM MyTable
WHERE ISDATE(GOOFYDATE)=0

Everything above adds up.

In Informix,

SELECT COUNT(1)
FROM MyTable
WHERE DATE(GOOFYDATE) IS NULL

gives me the Grand Total, as before. However, the following does, too:

SELECT COUNT(1)
FROM MyTable
WHERE DATE(GOOFYDATE) IS NULL
AND GOOFYDATE IS NULL

How may I implement in Informix my ISDATE goal, as accomplished above in SQL Server?

Upvotes: 0

Views: 946

Answers (1)

RET
RET

Reputation: 9188

You can write a stored procedure/function to perform this task, so that it will work exactly like the SQL Server equivalent. Something like:

CREATE FUNCTION informix.isdate(str VARCHAR(50), fmt VARCHAR(50))
    RETURNING SMALLINT;
    DEFINE d DATE;

    ON EXCEPTION IN (-1277,-1267,-1263) -- other errors are possible
        RETURN 0;
    END EXCEPTION;

    LET d = TO_DATE(str, fmt); -- acceptable date if exception not raised
    IF d < MDY(1,1,1850) THEN -- dates prior to this are "logically invalid"
        RETURN 0;
    END IF;
    RETURN 1;
END FUNCTION;

Which you can use thus:

-- Sample data
CREATE TEMP TABLE test1 (str VARCHAR(50));
INSERT INTO test1 VALUES ("not a date");
INSERT INTO test1 VALUES ("02/25/2016");
INSERT INTO test1 VALUES ("02/30/2016");
INSERT INTO test1 VALUES ("02/01/0000");

SELECT str, ISDATE(str, "%m/%d/%Y") FROM test1;

str                                                (expression)

not a date                                                    0
02/25/2016                                                    1
02/30/2016                                                    0
02/01/0000                                                    0

4 row(s) retrieved.

SELECT str AS invalid_date
  FROM test1
  WHERE ISDATE(str, "%m/%d/%Y") = 0;

invalid_date

not a date
02/30/2016
02/01/0000

3 row(s) retrieved.

Depending on how goofy your dates are, you may find other errors crop up. Just adjust the ON EXCEPTION clause accordingly. I've written this function to be as general purpose as possible, but you could code the "accepted" date format into the routine rather than pass it as an argument. (I don't recommend that, though.)

Upvotes: 3

Related Questions