Hi - I am working on joining two data sets together in quicksight with the join key being a variable ‘Date’ type datetime. I have uploaded CSV files to S3 and created both tables in Athena. In my first table the original data has two columns - year and month - both in string format. The second data set contains variable ‘date’ the type being datetime. I am attempting to concat the variables in table 1 with: concat(year, ‘/’, month, ‘/01’) as DateString. this returns the date in the correct format ‘yyyy/MM/dd’ but as type string. What is the next step to converting the type to date in the custom query option in Quicksight?
Hi @isabella - Can you please try to use parsedate to convert a string to date. See the documentation below.
If you can give some sample data for both data sets, it will be easy to guide you.
Regards - Sanjeeb
when i attempt to use parsedate i get this error in return:
Your database generated a SQL exception. This can be caused by query timeouts, resource constraints, unexpected DDL alterations before or during a query, and other database errors. Check your database settings and your query, and try again.
This is the what I have:
select *
parseDate(concat(year, ‘/’, month, ‘/01’), ‘yyyy/MM/dd’) as date1
from table1
Table 1:
|year - string | month -string | temperature - int | |
|2022 | 12 | 67 | |
|2022 | 11 | 66 | |
Table 2:
|date -datetime | humidity - int | |
|2022-12-01 | 67 | |
|2022-11-01 | 66 | |
Hi @isabella - No problem, can you please try the below. We need to put the format as per athena syntax.
select *,
date_parse(concat(year, ‘/’, month, ‘/01’), ‘%Y/%m/%d’) as date1
from table1
see the below link - sql - How to successfully convert string to date type in AWS Athena? - Stack Overflow
If you are creating a calculated field, then you can use QuickSight functions which is parsedate, sorry for the confusion.
Regards - Sanjeeb
this makes sense. thank you for your help!
1 Like