Search code examples
sqltype-conversionamazon-redshiftto-date

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

Solution

  • 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