Search code examples
sqlamazon-athenaprestotrino

How do I add leading zero to a 7 digit date using When clause in SQL

I have a column of dates, which have some 7 digits and 8 digit characters as type string.

  dates_stamp
  9022023
  10012023
  14012023
  4122022

How do I add a leading zero to dates?

select CASE
        WHEN last_contact_date BETWEEN 0 AND 9999999
        THEN ('0' , CAST(last_contact_date  as VARCHAR(8)))
        ELSE "FAIL"
         END
FROM table1

This is my concept but not sure how to make it work

Solution

  • String functions in Amazon Athena are based on Trino. It contains an lpad function that should suit your needs.

    lpad(Cast(last_contact_date as VarChar(8), last_contact_date, 8, '0')

    I would strongly suggest complying with the ISO 8601 international standard. "YYYY-MM-DD" retains a way to sort after conversion to string.

    substring(to_iso8601(last_contact_date),1,10)