CODEWITHSUNDEEP
sqltype-conversionamazon-redshiftto-date
Matt
Matt

Reputation: 15071

Redshift can't convert a string to a date, tried multiple functions

I have a table with a field called ADATE, it is a VARCHAR(16) and the values are like so: 2019-10-22-09:00.

I am trying to convert this do a DATE type but cannot get this to work.

I have tried:

1

TO_DATE(ADATE, 'YYYY-MM-DD')

Can't cast database type date to string

2

TO_DATE(LEFT(ADATE, 10), 'YYYY-MM-DD')

Can't cast database type date to string

3

TO_DATE(TRUNC(ADATE), 'YYYY-MM-DD')

XX000: Invalid digit, Value '-', Pos 4, Type: Decimal

4

CAST(ADATE AS DATE)

Error converting text to date

5

CAST(LEFT(ADATE, 10) AS DATE)

Error converting text to date

6

CAST(TRUNC(ADATE) AS DATE)

Error converting numeric to date

Upvotes: 1

Views: 8520

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Clearly, you have bad date string values -- which is why the value should be stored as a date to begin with.

I don't think Redshift has a way of validating the date before attempting the comparison, or of avoiding an error. But you can use case and regular expressions to see if the value is reasonable. This might help:

(case when left(adate, 10) ~ '^(19|20)[0-9][0-9]-[0-1][0-9]-[0-3][0-9]$'
      then to_date(left(adate, 10), 'YYYY-MM-DD')
 end)

This is not precise . . . you can make it more complex so month 19 is not permitted (for instance), but it is likely to catch the errors.

Upvotes: 2

Matt
Matt

Reputation: 15071

The issue was the data containing blanks (not Nulls) so the error was around them.

I resolved this by using the following code:

TO_DATE(LEFT(CASE WHEN adate = '' THEN NULL ELSE adate END, 10), 'YYYY-MM-DD') adate

Upvotes: 3

Related Questions