CODEWITHSUNDEEP
sqlhivebigdata
chandu
chandu

Reputation: 43

How to convert a date in hive with no leading zeroes

I have a string column, which has value like '5/1/21' , '5/14/21' which is of format day/month/year , but the column values doesn't have leading zero's like '05/01/21' , '05/14/21'

I have tried

select from_unixtime(unix_timestamp('5/15/21','MM/DD/YY'),'DD-MM-YYYY')
from  dev_irloi_wrk.Final_Ryder_data

but its giving the values like '2021-12-27' am unable to understand from where 12 and 27 are coming and how to get to the format '2021-05-14' , '2021-05-01'

Can anyone please help me.

Upvotes: 1

Views: 868

Answers (2)

the sunrise purchase sunset
the sunrise purchase sunset

Reputation: 11

select from_unixtime(unix_timestamp('5/15/21','mm/dd/yy'),'mm/dd/YYYY');
+-------------+
|     _c0     |
+-------------+
| 05/15/2021  |
+-------------+
1 row selected (0.397 seconds)

Upvotes: 1

leftjoin
leftjoin

Reputation: 38335

Hive uses SimpleDateFormat. Date and time patterns are documented here: SimpleDateFormat

Use M/dd/yy or M/d/yy, case is important.

y or yy - year parsing logic:

For parsing with the abbreviated year pattern ("y" or "yy"), SimpleDateFormat must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the time the SimpleDateFormat instance is created. For example, using a pattern of "MM/dd/yy" and a SimpleDateFormat instance created on Jan 1, 1997, the string "01/11/12" would be interpreted as Jan 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964.

Upvotes: 0

Related Questions