Comparing dates in DB2-CodePudding

I have a query where I am concatenating individual columns for day, month, year to form a date in the format MM/DD/YYYY and then using the newly concatenated column to get records after 12/16/2021 (Dec 16 2021).

I have tried multiple attempts at comparing dates but to no luck. Here's my query:

select x.* from
(
Select date(to_date(p.PLDRM || '/' || p.PLDRD || '/' || p.PLDRY, 'MM/DD/YYYY')) as Departure_Date,
       date(to_date(p.PLARM || '/' || p.PLARD || '/' || p.PLARY, 'MM/DD/YYYY')) as Arrival_Date,
       date(to_date(p.PLLCM || '/' || p.PLLCD || '/' || p.PLLCY, 'MM/DD/YYYY')) as Change_Date,
       p.*
from post_table p
) x
where x.change_date > date('12/16/2021')

This results in an error - Value in date, time or timestamp string not valid

CodePudding user response：

I'd be willing to bet that your tables have some invalid data...

A month of 0 for instance, or >= 13.

Another likely error is MM/DD of 02/30 or 02/31, or even 02/29 with a non-leap year.

You best bet is to define a User Defined Function (UDF) to build the dates for you, that way you can return NULL for invalid dates.

Optionally, you could check for specific invalid dates and return a valid value for your use case. For example, 02/30 ==> 02/28 or 02/29 if in a leap year.

I don't have any code example handy, but if I get some time I'll put something together and edit this answer.

UDF Example

CREATE OR REPLACE FUNCTION DateBuilder (
            mm INTEGER, dd INTEGER, yyyy INTEGER
    )
    RETURNS DATE
    LANGUAGE SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURNS NULL ON NULL INPUT
    BEGIN
        DECLARE invalidDate CONDITION FOR '22007';
        DECLARE EXIT HANDLER for invalidDate return NULL;
        return to_date(char(yyyy) concat '-' concat char(mm) concat '-' concat char(dd), 'YYYY-MM-DD');
    END;

CodePudding user response：

Update:

When querying distinct values for PLLCM, PLLCD, PLLCY I found there was '0' as one of the values (possibly to avoid having NULLS).

After tweaking the query to avoid '0' in those three columns the query returned results successfully.

CodePudding user response：

Same idea as @Charles, but uses a procedure because I could not use an handler in the body of a function back then (haven't checked if that works now):

CREATE OR REPLACE PROCEDURE castalesce_date_check_(IN strval VARCHAR(100))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
    DECLARE retval INT DEFAULT 1;
    DECLARE dateval DATE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
    SET dateval=DATE(strval);
    RETURN retval;
END @

CREATE OR REPLACE FUNCTION castalesce_date(strval VARCHAR(100))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
  DECLARE retval INT;
  -- The following is needed because declaration of SQLEXECPTION handlers
  -- isn't allowed in UDFs:
  CALL castalesce_date_check_(strval);
  GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
  IF retval = 1 THEN RETURN DATE(strval);
  ELSE RETURN NULL;
  END IF;
END @

db2 connect to ...
db2 -td@ -f ...

db2 "with post_table (PLDRY, PLDRM, PLDRD) as ( 
       values (1999, 12, 28),(1999,0,31),(2001,2,29) 
     ) select p.PLDRY, p.PLDRM, p.PLDRD 
     from post_table p 
     where castalesce_date(p.PLDRY || '-' || p.PLDRM || '-' || p.PLDRD) IS NULL"

PLDRY       PLDRM       PLDRD      
----------- ----------- -----------
       1999           0          31
       2001           2          29

2 record(s) selected.

It turned out that this can be greatly simplified these days:

CREATE OR REPLACE FUNCTION castalesce_date(strval VARCHAR(100))
     RETURNS DATE
BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
                RETURN NULL;
        END;
        RETURN date(strval);
END @

FWIW, I have several similar functions for other types such as XML. It's really handy when having to clean staging data.