How to change datetime to INT in snowflake?

Question

I have a table that contains a datetime column in datetime format, see picture for example.

I need to convert this timestamp to YYYYMMDD in int, something like this:

I'm trying this query, but it does not work.

select Cast(Cast(Cast (DATETIME AS DATE Format 'YYYYMMDD') AS VARCHAR(8))AS INT) AS DATE_KEY
  FROM TBL_A

Answer 1

• to_char converts a date using a format
• ::int casts to int

with data as (
    select current_timestamp() datetime
)


select to_char(datetime, 'YYYYMMDD')::int
from data

(with that said, I wouldn't recommend this type of int representation for a date)