ryan
0
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
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