I have a table in SQL with a column called some_date. This table has dates in the following format: YYYY-MM-DD (e.g. 2020-01-30). I tried to show a general example below:

CREATE TABLE my_table (some_date VARCHAR(10));

INSERT INTO my_table (some_date) VALUES
    ('2020-01-30'), ('2019-02-28'), ('2020-02-29'), ('2021-02-29'),
    ('2023-04-31'), ('2022-13-15'), ('2022-00-10'), ('2022-06-00'),
    ('2022-06'), ('2022-6-15'), ('abcd-ef-gh'), ('2023-07-25');

The problem is that some of these dates are invalid. For example, there are some instances of Feb 29th when that year does not have a Feb 29th. There are some instances where the date are like 2020-01-00. There are some instances where the dates are like 2020-01. In general, there are many such styles of anomalies in the dates. I am not able to list all types of anomalies.

I am wondering if there is some method to only select valid calendar dates.

I used the following method to make select dates like this:

SELECT *
FROM my_table
WHERE 
    REGEXP_LIKE(some_date, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')

But is there something else I can do to make sure that the date is a valid calendar date?

I tried to expand on this logic but it is still not complete.

SELECT *
FROM my_table
WHERE 
    REGEXP_LIKE(some_date, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
    AND SUBSTRING(some_date FROM 1 FOR 4)::INTEGER BETWEEN 1900 AND 2025
    AND SUBSTRING(some_date FROM 6 FOR 2)::INTEGER BETWEEN 1 AND 12
    AND SUBSTRING(some_date FROM 9 FOR 2)::INTEGER BETWEEN 1 AND 31;

Can someone help me out here? Can try_cast be useful here?

I have a table in SQL with a column called some_date. This table has dates in the following format: YYYY-MM-DD (e.g. 2020-01-30). I tried to show a general example below:

CREATE TABLE my_table (some_date VARCHAR(10));

INSERT INTO my_table (some_date) VALUES
    ('2020-01-30'), ('2019-02-28'), ('2020-02-29'), ('2021-02-29'),
    ('2023-04-31'), ('2022-13-15'), ('2022-00-10'), ('2022-06-00'),
    ('2022-06'), ('2022-6-15'), ('abcd-ef-gh'), ('2023-07-25');

The problem is that some of these dates are invalid. For example, there are some instances of Feb 29th when that year does not have a Feb 29th. There are some instances where the date are like 2020-01-00. There are some instances where the dates are like 2020-01. In general, there are many such styles of anomalies in the dates. I am not able to list all types of anomalies.

I am wondering if there is some method to only select valid calendar dates.

I used the following method to make select dates like this:

SELECT *
FROM my_table
WHERE 
    REGEXP_LIKE(some_date, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')

But is there something else I can do to make sure that the date is a valid calendar date?

I tried to expand on this logic but it is still not complete.

SELECT *
FROM my_table
WHERE 
    REGEXP_LIKE(some_date, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
    AND SUBSTRING(some_date FROM 1 FOR 4)::INTEGER BETWEEN 1900 AND 2025
    AND SUBSTRING(some_date FROM 6 FOR 2)::INTEGER BETWEEN 1 AND 12
    AND SUBSTRING(some_date FROM 9 FOR 2)::INTEGER BETWEEN 1 AND 31;

Can someone help me out here? Can try_cast be useful here?

• sql
• db2
• netezza

Share Improve this question Follow edited Mar 14 at 7:07 Mark Barinstein 12.6k22 gold badges1010 silver badges1818 bronze badges asked Mar 13 at 5:02 user439249user439249 14311 silver badge55 bronze badges 6

• 1 Have you tried try_cast? I imagine it does exatwhat you want – Dale K Commented Mar 13 at 5:33
• Seems like there is no try_cast but this duplicate seems to be asking the same question stackoverflow/questions/21483171/… – Dale K Commented Mar 13 at 5:42
• No proper DATE data type available? – jarlh Commented Mar 13 at 8:04
• 1 Does Netezza have a DATE data type? If it has, use it! – jarlh Commented Mar 13 at 9:36
• @user439249 I couldn't find a fiddle webseite to run a test using Netezza SQL. But I guess something like this should work: db<>fiddle. Could you please try this out and check if this or something similar satisfies your needs? – Jonas Metzler Commented Mar 13 at 9:40

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 1

This will be efficiently solved by using a tally table with all dates since the first year of your set of dates...

First create the tally table date as :

CREATE TABLE T_CALENDAR_CLD
(CLD_N            INT PRIMARY KEY,
 CLD_DATE         DATE UNIQUE,
 CLD_STRING_DATE  CHAR(10) UNIQUE);

second use a recursive query to fill this table :

WITH 
TC AS
(
--> fisrt year is 2000
SELECT 1 AS CLD_N, CAST('2000-01-01' AS DATE) AS CLD_DATE, CAST('2000-01-01' AS CHAR(10)) AS CLD_STRING_DATE
UNION ALL
SELECT CLD_N + 1, ADD_DAYS(CLD_DATE, 1), CAST(ADD_DAYS(CLD_DATE, 1) AS CHAR(10))
FROM   TC
WHERE  CLD_N < 365.2425 * 50 --> number of years
)
INSERT INTO T_CALENDAR_CLD
SELECT * 
FROM TC 

Third us a join to get only valid dates :

SELECT *
FROM   my_table AS mt
       JOIN T_CALENDAR_CLD AS TC
          ON mt.some_date = CLD_STRING_DATE;

Since some_date is stored as VARCHAR(10), you should convert it into a DATE and filter out invalid values.

SELECT some_date FROM my_table WHERE TRY_CAST(some_date AS DATE) IS NULL;

Please store dates in DATE or DATETIME format instead of VARCHAR.

This will ensure the database enforces valid dates automatically.