convert year and month as date format in hive

user_9567899 0
Viewed 946

I have year and month format as below. Year and Month are column names

Year  Month
2020  January
2019  October

and want to transform them so that they are in the following format

01-01-2020
01-10-2019

I have tried working with

cast(concat('01-',substring(month,1,3),'-',year) as timestamp) as Date_col

but couldn't make it work. Help is very much appreciated. Thank you!!

hiveql hive timestamp date sql
2 Answers

In SQL Server, you can achieve this by using DATEPART and FORMAT:

SELECT CONCAT('01-', FORMAT(DATEPART(MM, [Month] + ' 01 ' + [Year]), '0#'), '-', [Year]) AS Date_col
FROM yourTable
user_58095110

You are trying to cast 01-Jan-2019 to timestamp, but even if it worked, timestamp in Hive is not in format required: dd-MM-yyyy. timestamp has this format: 'yyyy-MM-dd HH:mm:ss.SSSSSSSSS' (nanoseconds are optional).

unix_timestamp function converts given format string to unix timestamp in seconds, from_unix_time converts unix timestamp to given format.

Use from_unixtime(unix_timestamp(concat('01-',month,'-',year),'dd-MMM-yyyy'),'dd-MM-yyyy') to convert from 'dd-MMM-yyyy' to 'dd-MM-yyyy'

Demo:

with 
your_data as (
select stack(2, 
2020, 'January',
2019, 'October'
        ) as ( Year, Month)
)
select Year, Month, from_unixtime(unix_timestamp(concat('01-',month,'-',year),'dd-MMM-yyyy'),'dd-MM-yyyy')  as Date_col
from your_data
;

Result:

year    month     date_col
2020    January   01-01-2020
2019    October   01-10-2019

Also this will work (without concat with 01-, using 'MMM-yyyy' format):

from_unixtime(unix_timestamp(concat(month,'-',year),'MMM-yyyy'),'dd-MM-yyyy')

If you want to convert to true Timestamp, do the same without specifying output format:

from_unixtime(unix_timestamp(concat(month,'-',year),'MMM-yyyy')) --returns 2020-01-01 00:00:00

Use SimpleDateFormat class docs as a format reference

user_27003440
Related