CODEWITHSUNDEEP
stringdatehadoophivebigdata
smp97
smp97

Reputation: 63

How to convert a string datatype column to date format in hive

Could you please guide with below query. I need to convert below string column to date.

Input and expected output is provided in screenshot.

1

Input table: column maturity_date is in string datatype. I tried below but not working as expected to_date(from_unixtime(unix_timestamp(maturity_date,'MM/DD/YYYY H:mm:ss'),'yyyy-mm-dd')

Upvotes: 1

Views: 529

Answers (2)

mck
mck

Reputation: 42332

Try using lower case letters. Upper case means another thing (day of year (D) and week-year (Y)).

to_date(from_unixtime(unix_timestamp(maturity_date,'MM/dd/yyyy H:mm:ss'),'yyyy-MM-dd')

Upvotes: 1

leftjoin
leftjoin

Reputation: 38290

Correct input format is 'MM/dd/yyyy H:mm:ss', not 'MM/DD/YYYY H:mm:ss'

Correct output format is yyyy-MM-dd, not yyyy-mm-dd. mm is minutes. MM is month

Read more about date format used in Hive here SimpleDateFormat

Upvotes: 1

Related Questions