CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
fjjones88
fjjones88

Reputation: 349

Create date from integers in separate fields in athena aws

I'm super new to athena, so bear with me. I have data stored as integers in three separate columns for year, month and day, as such:

year   month   day
2020   7       10
2020   7       11
2020   7       12

I'd like to turn these three fields into one date. How do I do that?

Thanks in advance!

Upvotes: 6

Views: 6571

Answers (2)

asantz96
asantz96

Reputation: 619

You have to use the concat() function. You can see the documentation here.

Depending of the format that you want to use, this can change.

concat(year, '-' , month , '-', day)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270351

One method is:

select date_parse(cast(year * 10000 + month * 100 + day as varchar(255)), '%Y%m%d')

This should also work:

select date(year || '-' || month || '-' || day)

Upvotes: 9

Related Questions