Search code examples
sqldatehivecastingteradata

Query teradata to hive

I have this teradata query, but when I run it on hive it doesn't support it

CAST(DATE '1900-01-01'+CAST( 999999999 - TRIM(BASM_DATE) AS INTEGER)  
AS DATE) AS BASM_DATE

Error

Error while compiling statement: FAILED: SemanticException line 
0:undefined:-1 Wrong arguments 'BASM_DATE': No matching method for class 
org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPDTIPlus with (date, 
int)

can you guys tell me which part to fix and what the query will be?

Thank You.

Solution

  • You need to use date_add(dt,num).

    Pls use below SQL -

    date_add( '1900-01-01',  999999999 - cast( TRIM(BASM_DATE) as INT) ) AS BASM_DATE

    I assumed BASM_DATE is a string column and using TRIM you are trying to remove trailing or leading spaces.