I want to convert the below column's format into MM/dd/yyyy HH:mm:ss in Hive

CodePudding user response：

Use from_unixtime(unix_timestamp(maturity_date,format_from),format_to) coalesce:

with mydata as (--example of dates to be converted to MM/dd/yyyy HH:mm:ss
select '3/22/2022 0:00:00' as Maturity_Date union all
select '11-08-21 0:00' union all
select '09-07-21' union all
select '10/27/2023 0:00:00'  
)

select coalesce(from_unixtime(unix_timestamp(Maturity_Date,'M/dd/yyyy H:mm:ss'), 'MM/dd/yyyy HH:mm:ss'),
                from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy H:mm'), 'MM/dd/yyyy HH:mm:ss'),
                from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy'), 'MM/dd/yyyy HH:mm:ss')
               )
from mydata

Result:

03/22/2022 00:00:00
08/11/2021 00:00:00
07/09/2021 00:00:00
10/27/2023 00:00:00

You may want to perform more strict checks to detect format, use case when ... then...:

  case when Maturity_Date rlike '^([1-9]|1[012])/(0[1-9]|[12][0-9]|3[01])/((19|20)\\d\\d) \\d:\\d{2}:\\d{2}$' 
            then from_unixtime(unix_timestamp(Maturity_Date,'M/dd/yyyy H:mm:ss'), 'MM/dd/yyyy HH:mm:ss')
       when Maturity_Date rlike '^(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])-(\\d{2}) \\d:\\d{2}$' 
            then from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy H:mm'), 'MM/dd/yyyy HH:mm:ss')
       when Maturity_Date rlike '^(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])-(\\d{2})$'
            then from_unixtime(unix_timestamp(Maturity_Date,'dd-MM-yy'), 'MM/dd/yyyy HH:mm:ss')
  end