How to convert a NUMERIC field into a DATE field and use it in a WHERE clause in DB2?-CodePudding

I have a field that is integer format 20220801 that needs to be converted to a date field. I then need to use this field in a WHERE clause compared against the CURRENT DATE. This is specifically for DB2.

Every time I try to do this I receive this error message:

Here are some snippets I've tried unsuccessfully, each time returning the above error

SELECT
    DATE(TIMESTAMP_FORMAT(CHAR(BWDUED), 'YYYYMMDD')) AS DUE_DATE,
    CURRENT DATE AS TODAY_DATE
FROM
    SCHEMA.TABLE
WHERE
    DATE(TIMESTAMP_FORMAT(CHAR(BWDUED), 'YYYYMMDD')) = CURRENT_DATE

SELECT
    DATE(TO_DATE(CHAR(BWDUED), 'YYYYMMDD')) AS DUE_DATE,
    CURRENT DATE AS TODAY_DATE
FROM
    SCHEMA.TABLE
WHERE
    DATE(TO_DATE(CHAR(BWDUED), 'YYYYMMDD')) = CURRENT_DATE

I've looked at many of the answers on here, but none of them have gotten me past this error. Any help on navigating this would be appreciated!

CodePudding user response：

Take a look at TIMESTAMP_FORMAT. It allows to specify the input format and you get a TIMESTAMP or DATE back.

VALUES (TIMESTAMP_FORMAT('20220801','YYYYMMDD'))

CodePudding user response：

The problem is because some row contains a number which can't be formatted as a timestamp with the specified pattern.

Consider the following example returning exactly the same error, if you uncomment any of the commented out lines.

SELECT
    DATE (TIMESTAMP_FORMAT (CHAR(BWDUED), 'YYYYMMDD')) AS DUE_DATE,
    CURRENT DATE AS TODAY_DATE
FROM
    (
    VALUES 
      --0 ,
      --20221232, 
      INT (TO_CHAR (CURRENT DATE, 'YYYYMMDD'))
    ) T (BWDUED)
WHERE
    DATE (TIMESTAMP_FORMAT (CHAR(BWDUED), 'YYYYMMDD')) = CURRENT_DATE

The solution would be create a "safe" formatter function eating possible errors, and use it instead of TIMESTAMP_FORMAT (or synonym TO_DATE function).

CREATE OR REPLACE FUNCTION TO_DATE_SAFE 
(
  P_STR VARCHAR (128)
, P_FMT VARCHAR (128)  
)
RETURNS TIMESTAMP
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC 
BEGIN 
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN 
    RETURN CAST (NULL AS TIMESTAMP);
  END;
  RETURN TO_DATE (P_STR, P_FMT);
END