0

I would like to convert date in string format ‘mmddyy’ (120618) to date and find the max of date in an Athena table. How can write this sql query ?

GMB
  • 216,147
  • 25
  • 84
  • 135
Punter Vicky
  • 15,954
  • 56
  • 188
  • 315

2 Answers2

1

You can use date_parse() to convert the string into a date:

select date_parse('120618', '%c%d%y')

Therefore, you could use it like this:

SELECT
  MAX(date_parse(date_field, '%c%d%y')) as dt
FROM table

See: Date and Time Functions and Operators — Presto Documentation

By the way, you should tell whoever made that original data format that it is a poor way to store dates. ISO format is better (eg 2018-12-06).

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
0

Can't you just output your date by using the ORDER BY , plus ASC | DESC (depends on what you mean by "max of date"), command and just use the first output you get? By using LIMIT?

StevyOcean
  • 1
  • 1
  • The date is in string format. I’m trying to do select max(order_date) from table. But since order_date is in string format , the maximum value is not returned – Punter Vicky Sep 10 '19 at 23:32