Truncate date values in Hive

Question

I have the following value (format) for a column in a Hive table :

2019-04-22 08:25:43.243754000000 (yyyy-MM-dd HH:mm:ss.SSSSSSSSSSSS)

Is there a way to truncate this value as below ? :

2019-04-22 08:25:43.243754 (yyyy-MM-dd HH:mm:ss.SSSSSS)

Thank you for your help.

Does this answer your question? [How to change date format in hive?](https://stackoverflow.com/questions/37558697/how-to-change-date-format-in-hive) — arghtype, Mar 20 '20 at 00:50
@arghtype Using unix_timestamp and from_unixtime is an overkill in this case — leftjoin, Mar 20 '20 at 06:22

score 0 · Accepted Answer · answered Mar 20 '20 at 06:20

Remove zeros at the end using regexp or substr(if you want fixed length).

Regexp example:

select regexp_replace('2019-04-22 08:25:43.243754000000','0*$','')

Result:

2019-04-22 08:25:43.243754

Using substr:

select substr('2019-04-22 08:25:43.243754000000',1,26)

1 Answers1