Search code examples
sqlprestotrino

Convert yyyyMMdd string into datetime in Presto

I have a birthdate column with date in form of '20100731', I want to convert it into datetime therefore I can get its user's age.

I've tried multiple casting however no luck.

I can only think of doing

year(current_date) - cast(substr('20100731', 1,4) as int)

This works fine but I want to know way to convert 'yyyyMMdd' string into datetime format

Solution

  • We can try using DATE_PARSE to convert the string input into a date, then take the difference in years to the current date:

    SELECT DATE_DIFF('year', DATE_PARSE('20100731', '%Y%m%d'), CURRENT_DATE) AS age
FROM yourTable;