Convert YYYYMMDD String to Date in Impala

Question

I'm using SQL in Impala to write this query. I'm trying to convert a date string, stored in YYYYMMDD format, into a date format for the purposes of running a query like this:

SELECT datadate, 
       session_info
FROM database
WHERE datadate >= NOW() - INTERVAL 5 DAY
ORDER BY datadate DESC;

Since the >= NOW() - INTERVAL 5 DAY code won't work with the YYYYMMDD string, I'd like to find a way to convert that into a date format that will work with this type of query. My thought is that it should look something like this (based on similar questions about other SQL query editors), but it's not working in Impala:

SELECT datadate,
       session_info,
       convert(datetime, '20141008', 102) AS session_date
FROM database
WHERE session_date >= NOW() - INTERVAL 5 DAY
ORDER BY session_date DESC;

Anyone know how to do this in Impala?

EDIT:

I finally found a working solution to the problem. None of the attempts using configurations of CAST or CONVERT would work in Impala, but the below query solves the problem and is fully operational, allowing date math to be performed on a column containing string values:

SELECT datadate,
       session_info
FROM database
WHERE datadate >= from_unixtime(unix_timestamp(now() - interval 5 days), 'yyyyMMdd')
GROUP BY datadate
ORDER BY datadate DESC;

Answer 1

See Timestamp Literals on [Link Updated 2020-08-24]:

https://docs.cloudera.com/cdp-private-cloud-base/7.1.3/impala-sql-reference/topics/impala-literals.html

You need to add the dashes to your string so Impala will be able to convert it into a date/timestamp. You can do that with something like:

concat_ws('-', substr(datadate,1,4), substr(datadate,5,2), substr(datadate,7) )

which you can use instead of datadate in your expression.

Answer 2

Native way:

to_timestamp(cast(date_number AS STRING), 'yyyyMMdd')