Convert date in Databricks - JD Edwards Julian date to calendar date

Convert date in Databricks - JD Edwards Julian date to calendar date

CREATE FUNCTION yourdatabase.date_convert_j_to_c(gldgj INT)

RETURNS TIMESTAMP

RETURN date_sub(

    date_add(

        DAY,

        cast(gldgj AS VARCHAR(10)) % 1000,

        date_add(YEAR, cast(gldgj AS VARCHAR(10)) / 1000, cast('1900-01-01' AS timestamp))

    ),

    1

);