Convert date to integer

[elgeo]

Hello. Is there a way in Databricks sql to convert a date to integer? In Db2, there is days function DAYS - IBM Documentation .

For example '2023-03-01' is converted to 738580 value.

Thank you in advance

[-werners-]

I'd cast it to a unix timestamp.

https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.unix...

[SergeRielau · Accepted solution]

TRy this:

CREATE OR REPLACE FUNCTION days(dt DATE) RETURN unix_date(dt) - unix_date(DATE'0001-01-01') + 1;

SELECT current_date, days(current_date); 

2023-03-09 738588

I verified on Db2 for LUW and it matches up.