I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

Before coming up with other solutions, I would first ask if you have a date conversion table.
If you do have one, this makes things much easier.
If not, I HIGHLY recommend creating one.
This table would consist of an actual date field along with other values that link to that same date.
For example, your date field would be 2023/04/14 and you would have 2 legacy conversion field values for '2023/04/14' and 20230414.
Once this is place, a left outer join to this date conversion table would tell you which dates from the originating table are bad dates.

If you do not have a date conversion table, here is a function and queries that will quickly help you create one.
The function is called get_dates_in_range and it generates 1 row for every date between 2 given dates.
Its a function I created myself so please ask any questions if you have any.

CREATE OR REPLACE FUNCTION Get_Dates_In_Range(start_date DATE, end_date DATE)
RETURNS TABLE (
    DATES   DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
SET OPTION
    COMMIT = *NONE,
    DBGVIEW = *SOURCE
BEGIN
    
    RETURN
        WITH 
            a (DATE) AS
            ( 
             values start_date
             UNION ALL
                SELECT a.DATE + 1 DAY
                FROM a
                WHERE a.date < end_date
            )
            
            select a.DATE
            from a;
END;



Okay, with the function in place, we can give it a quick test for the exact cases you provided.
Feel free to alter date ranges being passed into the function.
1 row will have 1 actual date value, a character date value using slashes, and a numeric date value.
select dates, 
       translate(char(dates), '/', '-') as date_char, 
       INT(replace(char(dates), '-', '')) as date_num
from table(get_dates_in_range(current_date - 100 years, current_date + 100 years));


Create a table using this query.
Then use this new table to perform a left outer join where the actual date field IS NULL.
This query will create the table in qtemp, but again, I HIGHLY recommend having a table like this in production.

create table qtemp/date_conversion as (
    select dates as actual_date, 
           translate(char(dates), '/', '-') as date_char, 
           INT(replace(char(dates), '-', '')) as date_num
    from table(get_dates_in_range(current_date - 100 years, current_date + 100 years))
) with data;


With the date_conversion table in place, we can now left outer join to the table in question.
For this scenario I will use a temp table again just for an example.

create table qtemp/a (
    legacy_date char(10)
);

insert into a values ('2000/00/00'); --bad date
insert into a values ('2000/01/01'); --good date
insert into a values ('2000/04/14'); --good date

--should only return the bad date record
select * from a
left outer join date_conversion on date_char = legacy_date
where actual_date is null;

--showing the records with good dates
select legacy_date, actual_date from a
left outer join date_conversion on date_char = legacy_date;


Hope this helps. Let me know if you have any thoughts and/or questions.

-Mike Z

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

We have one of those already, thanks

Before coming up with other solutions, I would first ask if you have a date conversion table.
If you do have one, this makes things much easier.
If not, I HIGHLY recommend creating one.
This table would consist of an actual date field along with other values that link to that same date.
For example, your date field would be 2023/04/14 and you would have 2 legacy conversion field values for '2023/04/14' and 20230414.
Once this is place, a left outer join to this date conversion table would tell you which dates from the originating table are bad dates.

If you do not have a date conversion table, here is a function and queries that will quickly help you create one.
The function is called get_dates_in_range and it generates 1 row for every date between 2 given dates.
Its a function I created myself so please ask any questions if you have any.

CREATE OR REPLACE FUNCTION Get_Dates_In_Range(start_date DATE, end_date DATE)
RETURNS TABLE (
    DATES   DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
SET OPTION
    COMMIT = *NONE,
    DBGVIEW = *SOURCE
BEGIN
    
    RETURN
        WITH 
            a (DATE) AS
            ( 
             values start_date
             UNION ALL
                SELECT a.DATE + 1 DAY
                FROM a
                WHERE a.date < end_date
            )
            
            select a.DATE
            from a;
END;



Okay, with the function in place, we can give it a quick test for the exact cases you provided.
Feel free to alter date ranges being passed into the function.
1 row will have 1 actual date value, a character date value using slashes, and a numeric date value.
select dates, 
       translate(char(dates), '/', '-') as date_char, 
       INT(replace(char(dates), '-', '')) as date_num
from table(get_dates_in_range(current_date - 100 years, current_date + 100 years));


Create a table using this query.
Then use this new table to perform a left outer join where the actual date field IS NULL.
This query will create the table in qtemp, but again, I HIGHLY recommend having a table like this in production.

create table qtemp/date_conversion as (
    select dates as actual_date, 
           translate(char(dates), '/', '-') as date_char, 
           INT(replace(char(dates), '-', '')) as date_num
    from table(get_dates_in_range(current_date - 100 years, current_date + 100 years))
) with data;


With the date_conversion table in place, we can now left outer join to the table in question.
For this scenario I will use a temp table again just for an example.

create table qtemp/a (
    legacy_date char(10)
);

insert into a values ('2000/00/00'); --bad date
insert into a values ('2000/01/01'); --good date
insert into a values ('2000/04/14'); --good date

--should only return the bad date record
select * from a
left outer join date_conversion on date_char = legacy_date
where actual_date is null;

--showing the records with good dates
select legacy_date, actual_date from a
left outer join date_conversion on date_char = legacy_date;


Hope this helps. Let me know if you have any thoughts and/or questions.

-Mike Z

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

David,
In the applications which use your date conversion table, how do you handle lookups which fail?

------------------------------
Robert Berendt IBMChampion

Original Message:
Sent: Mon April 17, 2023 10:12 AM
From: David Strawn
Subject: Date verification prior to casting

We have one of those already, thanks

------------------------------
David Strawn

Original Message:
Sent: Fri April 14, 2023 05:01 PM
From: Mike Zaringhalam
Subject: Date verification prior to casting

Before coming up with other solutions, I would first ask if you have a date conversion table.
If you do have one, this makes things much easier.
If not, I HIGHLY recommend creating one.
This table would consist of an actual date field along with other values that link to that same date.
For example, your date field would be 2023/04/14 and you would have 2 legacy conversion field values for '2023/04/14' and 20230414.
Once this is place, a left outer join to this date conversion table would tell you which dates from the originating table are bad dates.

If you do not have a date conversion table, here is a function and queries that will quickly help you create one.
The function is called get_dates_in_range and it generates 1 row for every date between 2 given dates.
Its a function I created myself so please ask any questions if you have any.

CREATE OR REPLACE FUNCTION Get_Dates_In_Range(start_date DATE, end_date DATE)
RETURNS TABLE (
    DATES   DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
SET OPTION
    COMMIT = *NONE,
    DBGVIEW = *SOURCE
BEGIN
    
    RETURN
        WITH 
            a (DATE) AS
            ( 
             values start_date
             UNION ALL
                SELECT a.DATE + 1 DAY
                FROM a
                WHERE a.date < end_date
            )
            
            select a.DATE
            from a;
END;



Okay, with the function in place, we can give it a quick test for the exact cases you provided.
Feel free to alter date ranges being passed into the function.
1 row will have 1 actual date value, a character date value using slashes, and a numeric date value.
select dates, 
       translate(char(dates), '/', '-') as date_char, 
       INT(replace(char(dates), '-', '')) as date_num
from table(get_dates_in_range(current_date - 100 years, current_date + 100 years));


Create a table using this query.
Then use this new table to perform a left outer join where the actual date field IS NULL.
This query will create the table in qtemp, but again, I HIGHLY recommend having a table like this in production.

create table qtemp/date_conversion as (
    select dates as actual_date, 
           translate(char(dates), '/', '-') as date_char, 
           INT(replace(char(dates), '-', '')) as date_num
    from table(get_dates_in_range(current_date - 100 years, current_date + 100 years))
) with data;


With the date_conversion table in place, we can now left outer join to the table in question.
For this scenario I will use a temp table again just for an example.

create table qtemp/a (
    legacy_date char(10)
);

insert into a values ('2000/00/00'); --bad date
insert into a values ('2000/01/01'); --good date
insert into a values ('2000/04/14'); --good date

--should only return the bad date record
select * from a
left outer join date_conversion on date_char = legacy_date
where actual_date is null;

--showing the records with good dates
select legacy_date, actual_date from a
left outer join date_conversion on date_char = legacy_date;


Hope this helps. Let me know if you have any thoughts and/or questions.

-Mike Z

------------------------------
Mike Zaringhalam

Original Message:
Sent: Fri April 14, 2023 02:52 PM
From: David Strawn
Subject: Date verification prior to casting

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

------------------------------
David Strawn
------------------------------

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

I assume your problem is, that an invalid character or numeric date returns an error value when casting it into a real date, that cannot be handled with SQL.
If you use TRY_CAST instead of CAST it will return a NULL value for invalid character or numeric dates ... and NULL values can be handled!
Example:

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

I assume your problem is, that an invalid character or numeric date returns an error value when casting it into a real date, that cannot be handled with SQL.
If you use TRY_CAST instead of CAST it will return a NULL value for invalid character or numeric dates ... and NULL values can be handled!
Example:

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

So the next question is:  Can you set the invalid date value to low value (i.e. 0001-01-01) instead of null?

I assume your problem is, that an invalid character or numeric date returns an error value when casting it into a real date, that cannot be handled with SQL.
If you use TRY_CAST instead of CAST it will return a NULL value for invalid character or numeric dates ... and NULL values can be handled!
Example:

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

Use the COALESCE function to convert null values to another value.

--replace the null in this sql statement with the value in question.
values COALESCE(null, date('0001-01-01'));

So the next question is:  Can you set the invalid date value to low value (i.e. 0001-01-01) instead of null?

I assume your problem is, that an invalid character or numeric date returns an error value when casting it into a real date, that cannot be handled with SQL.
If you use TRY_CAST instead of CAST it will return a NULL value for invalid character or numeric dates ... and NULL values can be handled!
Example:

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

Dear David

Regarding DB2i SQL function TRY_CAST that Birgitta suggested, please be informed that you need to run IBM i 7.5 to use it. Or if you are at IBM i 7.4, you need to apply a TR PTF SF99704 Level 20 to use it. No such function in earlier IBM i release.   Here is an additional IBM Technote on TRY_CAST :  https://www.ibm.com/support/pages/node/6575533 

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?

we are on 7.4, thanks for the heads up Satid...

Dear David

Regarding DB2i SQL function TRY_CAST that Birgitta suggested, please be informed that you need to run IBM i 7.5 to use it. Or if you are at IBM i 7.4, you need to apply a TR PTF SF99704 Level 20 to use it. No such function in earlier IBM i release.   Here is an additional IBM Technote on TRY_CAST :  https://www.ibm.com/support/pages/node/6575533 

I am looking at legacy files that have bad dates (i.e. 2000/00/00 or 20000000).  is there a way to verify a "date" (char or numeric column) prior to casting it to a date data type in a select statement?