could not convert from character to date type

Database
N/A

could not convert from character to date type

hi,

executing

SEL CAST (CAST ((CASE WHEN ( TRIM(date123) = '' ) THEN NULL ELSE (TRIM(date123)) END) AS CHAR(10) ) AS DATE FORMAT 'MM/DD/YYYY') from database.table gives the error.

SELECT Failed. 2665:  Invalid date.

quering the table for the col gives the result

sel date123 from database.table

gives

--------**********---------

12/3/1984

5/1/2000

?

10/1/1977

11/6/2008

6/1/1961

9/20/1971

5/1/1964

6/10/1998

?

12/22/2003

11/2/1992

10/16/1950

1/2/1970

4/1/1955

?

5/14/1951

12/3/2009

1/31/1968

11/8/2011

12/13/2004

5/9/1988

10/1/1973

2/15/1985

6/1/1985

--------**********---------

pls help...

Tags (1)
3 REPLIES
Enthusiast

Re: could not convert from character to date type

Hi,

By the way why you are using TRIM? Is the column of Character data type? 

If it is date When you apply the function TRIM(DATE123) it converts the date to string, You can only cast string into date if it is in the format

'YYYY-MM-DD'

So you have to first convert this Char date into the format 'YYYY-MM-DD'. 

Khurram
SMP
N/A

Re: could not convert from character to date type

Your insert fails because these are invalid dates:

In Teradata,  must give the two digit month and day for the date.For example  5/9/1988 isn't a vaild date. It must be 05/09/1988. Modify the code below as needed. This code was actually taken and modified from another post previously posted on the TD developer exchange.

SELECT

/*valid date */ ---works

CASE WHEN

(POSITION('-' IN x) = 3) AND

(POSITION('-' IN SUBSTRING(x FROM POSITION('-' IN x)+1 FOR 3)) = 3)

THEN

CAST (x AS DATE FORMAT 'DD-MM-YYYY')

/* Invalid date i.e.1-1-2011*/--works

WHEN

(POSITION('-' IN x) = 2) AND

(POSITION('-' IN SUBSTRING(x FROM POSITION('-' IN x)+1 FOR 3)) = 2)

THEN

CAST (('0' || SUBSTRING(x FROM 1 FOR 2) || '0' || SUBSTRING(x FROM 3 FOR 6)) AS DATE FORMAT 'DD-MM-YYYY')

/*invalid date i.e. '10-8-2011'*/--works

WHEN

(POSITION('-' IN x) = 3) AND

(POSITION('-' IN SUBSTRING(x FROM POSITION('-' IN x)+1 FOR 3)) = 2)

THEN

CAST ((SUBSTRING(x FROM 1 FOR 3) || '0' || SUBSTRING(x FROM 4 FOR 6)) AS DATE FORMAT 'DD-MM-YYYY')

/*invalid date i.e. 1-10-2011 */  ---works

WHEN

(POSITION('-' IN x) = 2) AND

(POSITION('-' IN SUBSTRING(x FROM POSITION('-' IN x)+1 FOR 3)) = 3)

THEN

CAST (('0' || SUBSTRING(x FROM 1 FOR 2) || SUBSTRING(x FROM 3 FOR 7)) AS DATE FORMAT 'DD-MM-YYYY')

ELSE

CAST ('01-01-0001' AS DATE FORMAT 'DD-MM-YYYY')-----used as a catch all if none of the above conditions have been meet.

END,x

Junior Contributor

Re: could not convert from character to date type

I prefer LIKE over POSITION, it's simpler :-)

CAST(CASE
WHEN x LIKE '_/_/____' THEN '0' || SUBSTRING(x FROM 1 FOR 2) || '0' || SUBSTRING(x FROM 3 FOR 6)
WHEN x LIKE '_/__/____' THEN '0' || SUBSTRING(x FROM 1 FOR 2) || SUBSTRING(x FROM 3 FOR 7)
WHEN x LIKE '__/_/____' THEN SUBSTRING(x FROM 1 FOR 3) || '0' || SUBSTRING(x FROM 4 FOR 6)
ELSE x
END AS DATE FORMAT 'mm/dd/yyyy')

Btw, TO_DATE allows single digit days, but not single digit months, then it's down to two possibilities.

And TPT 14+ supports VARDATE (which accepts single digits) to clean up that mess during load without additional stuff.

Dieter