hive string to date over lead function

Question

So guys, I have to convert this from PL/SQL (Oracle) into Hive format:

NVL(TO_CHAR(TO_DATE(SUBSTR(LEAD(DATE_TIME) OVER (PARTITION BY CU_NO ORDER BY CR_NO ,TO_NUMBER(DATE_TIME)),1,6),'YYMMDD'),'YYYYMMDD'),'99991231') END_DATE

I've already tried:

NVL(cast(from_unixtime(UNIX_TIMESTAMP(SUBSTR(LEAD(DATE_TIME) OVER (PARTITION BY CU_NO ORDER BY CR_NO ,TO_NUMBER(DATE_TIME)),1,6), 'yyMMdd' ),'yyyyMMdd') as string)'99991231') END_DATE

It always return for missing EOF at ')' near 'yyyyMMdd'. What went wrong?

score 0 · Answer 1 · answered Nov 30 '20 at 07:51

NVL(cast(from_unixtime(UNIX_TIMESTAMP(SUBSTR(LEAD(DATE_TIME) OVER (PARTITION BY CU_NO ORDER BY CR_NO ,TO_NUMBER(DATE_TIME)),1,6), 'yyMMdd' ),'yyyyMMdd') as string) missing-comma, '99991231')

with indented formatting:

NVL(
    cast(
        from_unixtime(
            UNIX_TIMESTAMP(
                SUBSTR(
                    LEAD(DATE_TIME) OVER (PARTITION BY CU_NO
                                          ORDER BY CR_NO, 
                                                   TO_NUMBER(DATE_TIME)
                                         ),
                    1,6
                ),
                'yyMMdd'
            ),
            'yyyyMMdd'
        ) as string
    ),
'99991231'
)

score 0 · Answer 2 · answered Nov 30 '20 at 08:51

You do not need extra to_number() in the order by:

NVL(cast(from_unixtime(UNIX_TIMESTAMP(
                        SUBSTR(
                         LEAD(DATE_TIME) OVER (PARTITION BY CU_NO ORDER BY CR_NO, DATE_TIME)
                        ,1,6), 'yyMMdd')
            ,'yyyyMMdd') 
   as string), '99991231')

hive string to date over lead function

2 Answers2