Search code examples
sqldatecastingimpala

convert YYYYMMDD to YYYY-MM-DD and now() in impala

i have a query like this

select
sources,
table_name,
position
from prd_sri_datalake.sri_300_300_monitoring_daily_new
where cast(date_process as DATE FORMAT 'YYYY-MM-DD') 
= cast(now() as DATE FORMAT 'YYYY-MM-DD')
ORDER BY TABLE_NAME ASC

for date_process has the format YYYYMMDD which i want to display as YYYY-MM-DD. i want to display data where date_process is equal to the current time, but i try to cast in impala it can't. can anyone help me?

Thank you

Solution

  • if i understand correctly, you want to check if date_process = current_date or not ?

    You can convert date_process to timestamp without time like this to_timestamp(date_process,'yyyyMMdd') and then equate with current date without time like this trunc(now(),'DD'). Where clause would look like this

    where to_timestamp(date_process,'yyyyMMdd')= trunc(now(),'DD') --date to date match

    You can also do like you did - convert everything to yyyyMMdd and equate.

    where date_process= from_timestamp(now(),'yyyyMMdd') --string to string match

    EDIT : convert date_process from 'YYYYMMDD' to 'YYYY-MM-DD' can be done like this - convert this to timestamp first using to_timestamp and then convert to string using from_timestamp.

    from_timestamp(to_timestamp(date_process,'yyyyMMdd'),'yyyy-MM-dd') as date_process_str_yyyy_MM_dd