SERIALIZATION_ERROR: Could not serialize column 'date' of type 'timestamp(3)

Question

Hi All,

Before adding this question i have seen couple of responses for similar use cases but could not figure out the answers as im beginner in SQL coding. Im trying to query the table 'argo.data_lake_nike_audit_data_partitioned' which has the column name as 'date' and when im trying to get a sample of date, it is throwing me the following error. My query is pretty basic and im unable to get the any output. Please let me know how to overcome this error

My code: 
SELECT
    defect_associate_id,
    job_id,
    program,
    is_ml_high_confidence
FROM argo.data_lake_nike_audit_data_partitioned
WHERE is_ml_high_confidence = 'LOW'
AND program != 'ME-Pilot'
AND 'date' BETWEEN DATE_TRUNC('week', CURRENT_DATE)
                AND DATE_ADD('day', 6, DATE_TRUNC('week', CURRENT_DATE));

Error:Query Id: 9e04153d-f799-408c-8ce1-1f65235c4351
Message:[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. TYPE_MISMATCH: line 10:12: Cannot check if varchar(4) is BETWEEN date and date [Execution ID: f08a2c1b-0d7f-4b53-9ec3-dda330031f83]

Kedar Baratam · Answer

Hello,

The error you are facing below is complaining that you are trying to compare varchar(4) (a string of 4 charaters) with Date's.

```
Error:Query Id: 9e04153d-f799-408c-8ce1-1f65235c4351 Message:[Simba]AthenaJDBC An error has been thrown from the AWS Athena client. TYPE_MISMATCH: line 10:12: Cannot check if varchar(4) is BETWEEN date and date [Execution ID: f08a2c1b-0d7f-4b53-9ec3-dda330031f83]
```

As you might be aware you cannot use between for string within a range of dates. Both the data type of either side should be same or cast-able to the either value. In you query, the value for 'date' is been taken as a string as opposed to a date value which you might failing to pass in your application. Try the same query with actual values of date and it should work as expected.

Thank you and have a great day.

SERIALIZATION_ERROR: Could not serialize column 'date' of type 'timestamp(3)

Add your answer

Relevant content