String to Date conversion in hive

Requirement:

Generally we receive data from different sources which usually have different types of date formats. When we create a hive table on top of these data, it becomes necessary to convert them into date format which is supported by hive.

Hive support yyyy-MM-dd date format. So output format of all kinds of date should be yyyy-MM-dd. We are assuming input is in string data type but contains date as value .

Please refer below table to convert any date format into fixed format i.e yyyy-MM-dd .

Input column name: dt (String).Replace dt with your column name.

Input Format Code Output Format
ddMMyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMyyyy’))) yyyy-MM-dd
dd-MM-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MM-yyyy’))) yyyy-MM-dd
dd/MM/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MM/yyyy’))) yyyy-MM-dd
dd MM yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MM yyyy’))) yyyy-MM-dd
dd.MM.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MM.yyyy’))) yyyy-MM-dd
ddMMMyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMyyyy’))) yyyy-MM-dd
dd-MMM-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMM-yyyy’))) yyyy-MM-dd
dd/MMM/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMM/yyyy’))) yyyy-MM-dd
dd MMM yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMM yyyy’))) yyyy-MM-dd
dd.MMM.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMM.yyyy’))) yyyy-MM-dd
ddMMMMyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMMyyyy’))) yyyy-MM-dd
dd-MMMM-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMMM-yyyy’))) yyyy-MM-dd
dd/MMMM/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMMM/yyyy’))) yyyy-MM-dd
dd MMMM yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMMM yyyy’))) yyyy-MM-dd
dd.MMMM.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMMM.yyyy’))) yyyy-MM-dd
ddMMyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMyy’))) yyyy-MM-dd
dd-MM-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MM-yy’))) yyyy-MM-dd
dd/MM/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MM/yy’))) yyyy-MM-dd
dd MM yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MM yy’))) yyyy-MM-dd
dd.MM.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MM.yy’))) yyyy-MM-dd
ddMMMyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMyy’))) yyyy-MM-dd
dd-MMM-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMM-yy’))) yyyy-MM-dd
dd/MMM/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMM/yy’))) yyyy-MM-dd
dd MMM yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMM yy’))) yyyy-MM-dd
dd.MMM.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMM.yy’))) yyyy-MM-dd
ddMMMMyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMMMyy’))) yyyy-MM-dd
dd-MMMM-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd-MMMM-yy’))) yyyy-MM-dd
dd/MMMM/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd/MMMM/yy’))) yyyy-MM-dd
dd MMMM yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd MMMM yy’))) yyyy-MM-dd
dd.MMMM.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’dd.MMMM.yy’))) yyyy-MM-dd
MMddyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMddyyyy’))) yyyy-MM-dd
MM-dd-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM-dd-yyyy’))) yyyy-MM-dd
MM/dd/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM/dd/yyyy’))) yyyy-MM-dd
MM dd yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM dd yyyy’))) yyyy-MM-dd
MM.dd.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM.dd.yyyy’))) yyyy-MM-dd
MMMddyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMddyyyy’))) yyyy-MM-dd
MMM-dd-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM-dd-yyyy’))) yyyy-MM-dd
MMM/dd/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM/dd/yyyy’))) yyyy-MM-dd
MMM dd yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM dd yyyy’))) yyyy-MM-dd
MMM.dd.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM.dd.yyyy’))) yyyy-MM-dd
MMMMddyyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMMddyyyy’))) yyyy-MM-dd
MMMM-dd-yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM-dd-yyyy’))) yyyy-MM-dd
MMMM/dd/yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM/dd/yyyy’))) yyyy-MM-dd
MMMM dd yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM dd yyyy’))) yyyy-MM-dd
MMMM.dd.yyyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM.dd.yyyy’))) yyyy-MM-dd
MMddyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMddyy’))) yyyy-MM-dd
MM-dd-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM-dd-yy’))) yyyy-MM-dd
MM/dd/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM/dd/yy’))) yyyy-MM-dd
MM dd yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM dd yy’))) yyyy-MM-dd
MM.dd.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MM.dd.yy’))) yyyy-MM-dd
MMMddyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMddyy’))) yyyy-MM-dd
MMM-dd-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM-dd-yy’))) yyyy-MM-dd
MMM/dd/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM/dd/yy’))) yyyy-MM-dd
MMM dd yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM dd yy’))) yyyy-MM-dd
MMM.dd.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMM.dd.yy’))) yyyy-MM-dd
MMMMddyy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMMddyy’))) yyyy-MM-dd
MMMM-dd-yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM-dd-yy’))) yyyy-MM-dd
MMMM/dd/yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM/dd/yy’))) yyyy-MM-dd
MMMM dd yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM dd yy’))) yyyy-MM-dd
MMMM.dd.yy to_date(from_unixtime(UNIX_TIMESTAMP(dt,’MMMM.dd.yy’))) yyyy-MM-dd
yyyyMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMdd’))) yyyy-MM-dd
yyyy-MM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MM-dd’))) yyyy-MM-dd
yyyy/MM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MM/dd’))) yyyy-MM-dd
yyyy MM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MM dd ‘))) yyyy-MM-dd
yyyy.MM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MM.dd’))) yyyy-MM-dd
yyyyMMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMMdd’))) yyyy-MM-dd
yyyy-MMM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MMM-dd’))) yyyy-MM-dd
yyyy/MMM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MMM/dd’))) yyyy-MM-dd
yyyy MMM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MMM dd ‘))) yyyy-MM-dd
yyyy.MMM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MMM.dd’))) yyyy-MM-dd
yyyyMMMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyyMMMMdd’))) yyyy-MM-dd
yyyy-MMMM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy-MMMM-dd’))) yyyy-MM-dd
yyyy/MMMM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy/MMMM/dd’))) yyyy-MM-dd
yyyy MMMM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy MMMM dd ‘))) yyyy-MM-dd
yyyy.MMMM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyyy.MMMM.dd’))) yyyy-MM-dd
yyMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMdd’))) yyyy-MM-dd
yy-MM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MM-dd’))) yyyy-MM-dd
yy/MM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MM/dd’))) yyyy-MM-dd
yy MM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MM dd ‘))) yyyy-MM-dd
yy.MM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MM.dd’))) yyyy-MM-dd
yyMMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMMdd’))) yyyy-MM-dd
yy-MMM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MMM-dd’))) yyyy-MM-dd
yy/MMM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MMM/dd’))) yyyy-MM-dd
yy MMM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MMM dd ‘))) yyyy-MM-dd
yy.MMM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MMM.dd’))) yyyy-MM-dd
yyMMMMdd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yyMMMMdd’))) yyyy-MM-dd
yy-MMMM-dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy-MMMM-dd’))) yyyy-MM-dd
yy/MMMM/dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy/MMMM/dd’))) yyyy-MM-dd
yy MMMM dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy MMMM dd ‘))) yyyy-MM-dd
yy.MMMM.dd to_date(from_unixtime(UNIX_TIMESTAMP(dt,’yy.MMMM.dd’))) yyyy-MM-dd

Don’t forget to see all tutorials Keep learning, keep growing .Subscribe us.

Load CSV file into hive AVRO table

Requirement You have comma separated(CSV) file and you want to create Avro table in hive on top of it, then ...
Read More

Load CSV file into hive PARQUET table

Requirement You have comma separated(CSV) file and you want to create Parquet table in hive on top of it, then ...
Read More

Hive Most Asked Interview Questions With Answers – Part II

What is bucketing and what is the use of it? Answer: Bucket is an optimisation technique which is used to ...
Read More
/ hive, hive interview, interview-qa

Spark Interview Questions Part-1

Suppose you have a spark dataframe which contains millions of records. You need to perform multiple actions on it. How ...
Read More

1 Comment

Leave a Reply