Getting NULL after combining strings between date functions

Given a date column with a value 2020-05-01, I want to return 2020-Q2. The QUARTER() function is not available due to the Hive version we are using.

I can get the quarter number with: (INT((MONTH(yyyy_mm_dd)-1)/3)+1). When I try to combine this with the YEAR() function and strings, I get null:

year(yyyy_mm_dd)+"-"+"Q"+(INT((MONTH(yyyy_mm_dd)-1)/3)+1) as time_frame

How can I properly concatenate this to get the desired string of null?

Using this query:

select
    yyyy_mm_dd,
    year(yyyy_mm_dd) 
        || '-Q' 
        || INT((MONTH(yyyy_mm_dd)-1)/3) + 1 as time_frame
from
    schema.table1
where
    yyyy_mm_dd = '2019-01-01'
limit 1

Throws this error:

Error while compiling statement: FAILED: ParseException line 4:9 cannot recognize input near ‘|’ ”-Q” ‘|’ in expression specification

Answer

This does the trick:

concat(year(yyyy_mm_dd), '-Q', INT((MONTH(yyyy_mm_dd)-1)/3) + 1) as time_frame

Advertisement

Answer