CODEWITHSUNDEEP
sqldateamazon-athenapresto
mrsquid
mrsquid

Reputation: 635

AWS Athena - How to change format of date string

I have a two tables in a database in AWS Athena that I want to join.

I want to join them by several columns, one of them being date.

However in one data set the date string is encoded for single value months is encoded as

 "08/31/2018"

While the other would have it encoded as 

 "8/31/2018"

Is there a way to make them the same format?

I am unsure if it is easier to add the extra 0 to strings which have lack the extra 0 or to concatenate strings which have the extra 0.

Based on what I have researched I think I will have to use the CASE and CONCAT functions.

Both of the tables were loaded into the database from a CSV file, and the variables are in the string format.

I have tried changing the values manually in the CSV file, tried running an R script on one of the tables to format the date in the same way, and have also tried re-loading the tables into the database as the same date format.

However no matter what I do whenever it is loaded into the database, even when they have the same date type, it always loads them with different formats.

One with the the extra 0 and the other without it.

The last avenue I haven't tried is through a SQL query.

However I am not well versed in Athena and am having a hard time formatting this query.

I know this is rather vague, so please ask me for more information if you need.

If someone could help me start this query I would be grateful.

Thank you for the help.

Here is the query for changing dates in Athena.

  date_parse(table.date_variable,'%m/%d/%Y')

Though Athena tables are immutable once created.

Upvotes: 1

Views: 8753

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can convert the value to date using date_parse(). So, this should work:

date_parse(t1.datecol, '%m/%d/%Y') = str_to_date(t2.datecol, '%m/%d/%Y')

Having said that, you should fix the data model. Store dates as dates not as strings! Then you can use an equality join and that is just better all around.

Upvotes: 1

Related Questions