Databricks Community

elgeo · ‎03-08-2023

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

SergeRielau · ‎03-09-2023

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.

-werners- · ‎03-09-2023

I'd cast it to a unix timestamp.

SergeRielau · ‎03-09-2023

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.

Convert date to integer