CODEWITHSUNDEEP
impala
EAPL Hyderabad
EAPL Hyderabad

Reputation: 23

Impala decimal value convert into date

I have a table in impala where DATE value is stored in decimal format in YYDDD format. e.g. 2020-01-25 is stored as 20025 or 2020-12-31 is stored as 20365 etc. How to convert it back into DATE and compare with today's date or between today and previous 12 months ?

Thanks

Upvotes: 1

Views: 495

Answers (1)

EAPL Hyderabad
EAPL Hyderabad

Reputation: 23

after various tries, I was able to get required output. here is how I managed. not efficient but working.

    concat('1', CAST(date_part('year', date_sub(now(), interval 12 months))-2000 as STRING), case when length(CAST(dayofyear(date_sub(now(), interval 12 months)) as STRING)) = 1 then 
        concat('00', CAST(dayofyear(date_sub(now(), interval 12 months)) as STRING))
    when length(CAST(dayofyear(date_sub(now(), interval 12 months)) as STRING)) = 2 then      
        concat('0', CAST(dayofyear(date_sub(now(), interval 12 months)) as STRING) )
    else CAST(dayofyear(date_sub(now(), interval 12 months)) as STRING)
    end) fromdate

Upvotes: 1

Related Questions