CODEWITHSUNDEEP
hivehiveql
thecardcaptor
thecardcaptor

Reputation: 131

hive string yymmdd to date type yyyymmdd

i have to convert this from string to date like this:

TO_CHAR(TO_DATE(SUBSTR(DATE_TIME,1,6),'YYMMDD'),'YYYYMMDD') as HI_DATE

I already try this format:

to_date(from_unixtime(UNIX_TIMESTAMP( substr(date_time,1,6), 'yyMMdd' ),'yyyy-MM-dd hh:mm:ss')) but it returns NULL

What went wrong? How to make this correctly?

Upvotes: 0

Views: 2916

Answers (1)

mck
mck

Reputation: 42422

You cannot specify hh:mm:ss in to_date. If you want the time you should use to_timestamp.

to_date(
    from_unixtime(
        UNIX_TIMESTAMP( 
            substr(date_time, 1, 6),
            'yyMMdd'
        ),
        'yyyy-MM-dd'
    )
)

Upvotes: 1

Related Questions