Hi,
i would like to convert string to date data type on Athena.
createdate = 2023-03-16T16:54:59.000+0000
1 i’m split to 2023-01-23
2 i’m need to convert string to date format 2023-01-23
3 this is my sql
select
date_parse(concat(SUBSTRING(ap.createddate, 1, 4),‘-’,SUBSTRING(ap.createddate, 6, 2),‘-’,SUBSTRING(ap.createddate, 9, 2)),‘%Y-%M-%d’) as date3
after i run script i got an error
Many thanks for help.
Have you tried
CAST(createddate AS TIMESTAMP)
Hi Max,
i’ve write sql as you suggest. however i got an error.
select
CAST(ap.createddate AS TIMESTAMP)
| region: | ap-southeast-1 |
|---|---|
| timestamp: | 1681138089793 |
| requestId: | 01066b56-776d-4d5c-b69a-2955723cf6b5 |
| sourceErrorCode: | 100071 |
| sourceErrorMessage: | [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. COLUMN_NOT_FOUND: line 2:300: Column ‘_col4’ cannot be resolved [Execution ID: a659c610-9bc8-4127-a5e6-f927941a87f0] |
| sourceErrorState: | HY000 |
| sourceException: | java.sql.SQLException |
| sourceType: | ATHENA |
Would u please help.
You need to name it
CAST(createddate AS TIMESTAMP) createddate
hi max
i already name it , but still error , it look like value can’t be cast.
createdate = 2023-03-16T16:54:59.000+0000
| region: | ap-southeast-1 |
|---|---|
| timestamp: | 1681141748291 |
| requestId: | 96836e8e-7ca5-41a3-ac99-84533ff51c99 |
| sourceErrorCode: | 100071 |
| sourceErrorMessage: | [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. INVALID_CAST_ARGUMENT: Value cannot be cast to timestamp: 2023-03-16T16:54:59.000+0000 [Execution ID: 4d1e9c84-e0fe-457e-adb2-0f8532fba314] |
| sourceErrorState: | HY000 |
| sourceException: | java.sql.SQLException |
| sourceType: | ATHENA |
Hi @Siriphon - Can you please use the below formula.
parseDate(substring({date_Field},1,10), ‘YYYY-MM-DD’)
I checked and it is giving me require result.
Regards - San
Hi, Sanjeeb2022
i’ve tried sql
parseDate(substring{ap.createddate},1,10),‘YYYY-MM-DD’, checkdate),
it’s not work , please help.
| region: | ap-southeast-1 |
|---|---|
| timestamp: | 1681146544789 |
| requestId: | ec597016-51d5-4c09-a950-f16eea552d5e |
| sourceErrorCode: | 100071 |
| sourceErrorMessage: | [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. line 10:20: mismatched input ‘{’. Expecting: ‘(’ [Execution ID not available] |
| sourceErrorState: | HY000 |
| sourceException: | java.sql.SQLException |
| sourceType: | ATHENA |
Athena uses PrestoDB
https://prestodb.io/docs/current/functions/datetime.html
date_parse(string, format) → timestamp
date_parse(substring(ap.createddate) ,1,10),‘%Y-%m-%d’) created_date
Thanks @thomask for the insights. Yes you are correct Athena uses presto in backend.
Hi @Siriphon - Can you please try Thomas suggestion. Also try the option
parseDate(substring({ap.createddate},1,10),‘YYYY-MM-DD’)
Regards - San
DATE(SUBSTRING(createddate,1,10)) should also work in presto
Hello @thomask thank you for your comment. I tried it out, but it does not work for me, I am getting the following error: [HY000][100071] [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. FUNCTION_NOT_FOUND: line 4:12: Unexpected parameters (timestamp(3), integer, integer) for function substring. Expected: substring(varchar(x), bigint, bigint), substring(varchar(x), bigint), substring(char(x), bigint), substring(char(x), bigint, bigint) [Execution ID: d47c3fc7-6fb2-4db6-a0c3-01aa8268f473]
Hi @Liudmila - Can you please share sample data from createdate? Looks like it is not a string.
if possible, give us a screenshot.
Regards - San
DATE(created) will give you just the Datepart of your timestamp.
Alternative: DATE_TRUNC(“day”, created)
Thanks @thomask for the details. Hi @Liudmila please confirm whether it fixed your issue or not.
Regards - San
it does, thank you a lot!
