sql - My date column is causing my query to crash, what is the issue? - Stack Overflow

admin管理员组

文章数量:1291216

I am running the query below but I need to remove the WHERE clause. It works fine with it, even filtering on other Cust_Name, but when I remove it I get an error saying "Invalid date supplied".

SELECT
CASE WHEN(Cust_PO_Date = '00000000') 
  THEN(null) 
  ELSE(CAST(Cust_PO_Date AS DATE FORMAT 'YYYYMMDD')) 
END AS Cust_PO_Date
Cust_Name,
Cust_Desc,
Cust_Qty

FROM
HTGPO_SPND_VIEWS.Seller_Invoice_Raw

WHERE 
Cust_Name = 'ABC'

My guess is that the data is corrupt? I've tried to do a TRYCAST function but that didn't work. When I ran a SELECT DISTINCT I noticed values in that column differ in formatting. Some are formatted as YYYYMMDD with no "-" where as some values include the "-", YYYY-MM-DD. Could the inclusion and exclusion of "-" be the issue? I guess this would make some values 8 characters vs 10. Also, if it helps, I noticed the datatype for this column is stored as a VARCHAR.

I tried to filter out invalid dates such as '00000000':

SELECT
CASE WHEN(Cust_PO_Date = '00000000') 
  THEN(null) 
  ELSE(CAST(Cust_PO_Date AS DATE FORMAT 'YYYYMMDD')) 
END AS Cust_Date
FROM
HTGPO_SPND_VIEWS.Seller_Invoice_Raw

I am running the query below but I need to remove the WHERE clause. It works fine with it, even filtering on other Cust_Name, but when I remove it I get an error saying "Invalid date supplied".

SELECT
CASE WHEN(Cust_PO_Date = '00000000') 
  THEN(null) 
  ELSE(CAST(Cust_PO_Date AS DATE FORMAT 'YYYYMMDD')) 
END AS Cust_PO_Date
Cust_Name,
Cust_Desc,
Cust_Qty

FROM
HTGPO_SPND_VIEWS.Seller_Invoice_Raw

WHERE 
Cust_Name = 'ABC'

My guess is that the data is corrupt? I've tried to do a TRYCAST function but that didn't work. When I ran a SELECT DISTINCT I noticed values in that column differ in formatting. Some are formatted as YYYYMMDD with no "-" where as some values include the "-", YYYY-MM-DD. Could the inclusion and exclusion of "-" be the issue? I guess this would make some values 8 characters vs 10. Also, if it helps, I noticed the datatype for this column is stored as a VARCHAR.

I tried to filter out invalid dates such as '00000000':

SELECT
CASE WHEN(Cust_PO_Date = '00000000') 
  THEN(null) 
  ELSE(CAST(Cust_PO_Date AS DATE FORMAT 'YYYYMMDD')) 
END AS Cust_Date
FROM
HTGPO_SPND_VIEWS.Seller_Invoice_Raw

• sql
• date
• teradata

Share Improve this question Follow asked Feb 13 at 17:21 Jack FrenchJack French 1111 silver badge 2

• 2 The correct way to fix this is to alter the column data type to DATE. – jarlh Commented Feb 13 at 17:41
• We need a minimal reproducible example with sample data that causes the issue – Dale K Commented Feb 13 at 20:14

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

You're going to have to play with regexes here. Here's a couple of simple examples, assuming you have either YYYY-MM-DD, or YYYYMMDD. I would use the regexes to determine how to convert the string to a date. The trycast is to eliminate values that match one of the regexes but aren't actual dates (like 00000000)

create volatile table vt_foo
(col1 varchar(10)
) on commit preserve rows;

insert into vt_foo values ('2025-01-01');
insert into vt_foo values ('20250201');
insert into vt_foo values ('00000000');

select 
col1, 
    case 
        when REGEXP_SIMILAR(col1,'([0-9]{4}-[0-9]{2}-[0-9]{2})') = 1  then cast(col1 as date)
        when REGEXP_SIMILAR(col1,'([0-9]{8})') = 1 then cast(col1 as date format 'YYYYMMDD')
    end

from vt_foo
where 
    trycast (
        case 
            when REGEXP_SIMILAR(col1,'([0-9]{4}-[0-9]{2}-[0-9]{2})') = 1 then col1
            when REGEXP_SIMILAR(col1,'([0-9]{8})') = 1 then substring(col1,1,4) || '-' || substring(col1,5,2) || '-' || substring(col1,7,2)
        end as date) is not null

Ideally you would use logic like this to store the value in an actual date column.

You can apply a RegEx to unify the formatting.

trycast(regexp_replace(cust_po_date, '(\d{4}).?(\d{2}).?(\d{2})', '\1-\2-\3') as date) as new_date

As TryCast doesn't support a FORMAT this simply adds the missing '-'.

To find bad data add a

where cust_po_date <> '00000000' 
  and new_date is null

本文标签： sqlMy date column is causing my query to crashwhat is the issueStack Overflow