Cast XLSX date format into timestamp

Platform
1

If you want to import XLSX file with Form V2, and there are column in XLSX date format (which is a float like 44554.68025463), you can use the function below :

CREATE OR REPLACE FUNCTION ii_dwh.f_ii_core_cast_date_xlsx_as_timestamp(p_input character varying)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE 
	var_result timestamp;
BEGIN
	
	-- CORE FUNCTION / 
	-- v1 / 2022.06.16 / NBLIN / Initial version
	-- This function cast input xslx date format (text) into timestamp.
	--	INPUT: value to cast as text
	--	OUTPUT: Result as timestamp (or NULL)
	
	SELECT 
		to_timestamp( 978307200.0 + ((p_input::float-36892) * 86400)  )
	INTO var_result;

	return var_result;
	
EXCEPTION when others then
	return null;  
END;
$function$
;

And use it like this

SELECT ii_dwh.f_ii_core_cast_date_xlsx_as_timestamp(“col_date_xlsx_text”)
FROM table