Convert epoch number to date and add 01/01/1970 to display the output in HIVE SQL

Question

I am trying hard to figure out how to convert epoch number (column name: effectivedate and datatype: string, value: 19186 ) to date format as mm/dd/yyyy and add '01/01/1970' to display the output in HIVE SQL.

For example: 19186 + 01/01/1970 should display 07/13/2022 as output

Answer 1

The function you are looking for is date_add(). The column effectivedate doesnt look like number of days from 1/1/1970. So using above function you can easily convert it to a full date.

SELECT date_add('1970-01-01', cast(effectivedate as BIGINT))

Test SQL SELECT date_add('1970-01-01', 19186 )