How to convert a date format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive?

Question

How to CONVERT a date in format YYYY-MM-DD into integer YYYYMMDD in Presto/Hive?

I am trying to convert the below list into YYYYMMDD integers

WITH  all_dates  as (SELECT
    CAST(date_column AS DATE) date_column
FROM
    (VALUES
        (SEQUENCE(FROM_ISO8601_DATE('2017-07-01'),
                  FROM_ISO8601_DATE('2017-11-15'),
                  INTERVAL '1' DAY)
        )
    ) AS t1(date_array)
CROSS JOIN
    UNNEST(date_array) AS t2(date_column)
    )

I tried something like this but it doesn't work

   SELECT
   CAST(
      CAST(year(date_column) AS VARCHAR(4)) +
      right('0' + CAST(month(date_column) AS VARCHAR(2)), 2) +
      right('0' + CAST(day(date_column) AS VARCHAR(2)), 2) 
   AS DATETIME)
   FROM all_dates

Solution 1

If you just need to transform your date YYYY-MM-DD into an integer YYYYMMDD why don't you try to first remove all the occurrences of "-" from the string representation of your date before casting the result to int by using something like this?

cast(regexp_replace(str_column,'-','') as int)

Solution 2

Also you can use date_format function:

hive> select cast(date_format('2017-07-01','yyyyMMdd') as int);
OK
20170701

Solution 3

Simply REPLACE the '-' with Empty string and CAST it into INT.

Try the following:

SELECT CAST(REPLACE(Date_Column,'-','') AS INT)

Solution 4

Are you sure you want to use YYYYMMDD ? Hive follows Java convention for Date Formatting and as per https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html you can see that YYYY represents week year which means you may get into trouble for end of the year dates. 2019 may appear as 2020.

Use yyyyMMdd instead.

@leftjoin has probably the correct respone.

beeline> select cast(date_format('2019-07-01','yyyyMMdd') as int);
OK
20190701

View more solutions