[string] Hive cast string to date dd-MM-yyyy

How can I cast a string in the format 'dd-MM-yyyy' to a date type also in the format 'dd-MM-yyyy' in Hive?

Something along the lines of:

CAST('12-03-2010' as date 'dd-mm-yyyy')

This question is related to string date casting hive

The answer is


from_unixtime(unix_timestamp('12-03-2010' , 'dd-MM-yyyy'))

If I have understood it correctly, you are trying to convert a String representing a given date, to another type.

Note: (As @Samson Scharfrichter has mentioned)

  • the default representation of a date is ISO8601
  • a date is stored in binary (not as a string)

There are a few ways to do it. And you are close to the solution. I would use the CAST (which converts to a DATE_TYPE):

SELECT cast('2018-06-05' as date); 

Result: 2018-06-05 DATE_TYPE

or (depending on your pattern)

select cast(to_date(from_unixtime(unix_timestamp('05-06-2018', 'dd-MM-yyyy'))) as date)

Result: 2018-06-05 DATE_TYPE

And if you decide to convert ISO8601 to a date type:

select cast(to_date(from_unixtime(unix_timestamp(regexp_replace('2018-06-05T08:02:59Z', 'T',' ')))) as date);

Result: 2018-06-05 DATE_TYPE

Hive has its own functions, I have written some examples for the sake of illustration of these date- and cast- functions:

Date and timestamp functions examples:

Convert String/Timestamp/Date to DATE

SELECT cast(date_format('2018-06-05 15:25:42.23','yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE
SELECT cast(date_format(current_date(),'yyyy-MM-dd') as date); -- 2018-06-05 DATE_TYPE
SELECT cast(date_format(current_timestamp(),'yyyy-MM-dd') as date);  -- 2018-06-05 DATE_TYPE

Convert String/Timestamp/Date to BIGINT_TYPE

SELECT to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE
SELECT to_unix_timestamp(current_date(),'yyyy/MM/dd HH:mm:ss'); -- 1528205000 BIGINT_TYPE
SELECT to_unix_timestamp(current_timestamp(),'yyyy/MM/dd HH:mm:ss'); -- 1528205142 BIGINT_TYPE

Convert String/Timestamp/Date to STRING

SELECT date_format('2018-06-05 15:25:42.23','yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE
SELECT date_format(current_timestamp(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE
SELECT date_format(current_date(),'yyyy-MM-dd'); -- 2018-06-05 STRING_TYPE

Convert BIGINT unixtime to STRING

SELECT to_date(from_unixtime(unixtime,'yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 STRING_TYPE

Convert String to BIGINT unixtime

SELECT unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP; -- 1528149600 BIGINT_TYPE

Convert String to TIMESTAMP

SELECT cast(unix_timestamp('2018-06-05 15:25:42.23','yyyy-MM-dd') as TIMESTAMP); -- 1528149600 TIMESTAMP_TYPE

Idempotent (String -> String)

SELECT from_unixtime(to_unix_timestamp('2018/06/05 15:25:42.23','yyyy/MM/dd HH:mm:ss')); -- 2018-06-05 15:25:42 STRING_TYPE

Idempotent (Date -> Date)

SELECT cast(current_date() as date); -- 2018-06-26 DATE_TYPE

Current date / timestamp

SELECT current_date(); -- 2018-06-26 DATE_TYPE
SELECT current_timestamp(); -- 2018-06-26 14:03:38.285 TIMESTAMP_TYPE

AFAIK you must reformat your String in ISO format to be able to cast it as a Date:

cast(concat(substr(STR_DMY,7,4), '-',
            substr(STR_DMY,1,2), '-',
     as date
     ) as DT

To display a Date as a String with specific format, then it's the other way around, unless you have Hive 1.2+ and can use date_format()

=> did you check the documentation by the way?

Let's say you have a column 'birth_day' in your table which is in string format, you should use the following query to filter using birth_day

date_Format(birth_day, 'yyyy-MM-dd')

You can use it in a query in the following way

select * from yourtable
date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';

This will convert the whole column:

select from_unixtime(unix_timestamp(transaction_date,'yyyyMMdd')) from table1

Questions with string tag:

How to split a string in two and store it in a field String method cannot be found in a main class method Kotlin - How to correctly concatenate a String Replacing a character from a certain index Remove quotes from String in Python Detect whether a Python string is a number or a letter How does String substring work in Swift How does String.Index work in Swift swift 3.0 Data to String? How to parse JSON string in Typescript How to change the datetime format in pandas How to write to a CSV line by line? convert string to number node.js "error: assignment to expression with array type error" when I assign a struct field (C) Remove 'b' character do in front of a string literal in Python 3 Ruby: How to convert a string to boolean What does ${} (dollar sign and curly braces) mean in a string in Javascript? How do I make a new line in swift converting json to string in python PHP - remove all non-numeric characters from a string C# - How to convert string to char? How can I remove the last character of a string in python? Converting std::__cxx11::string to std::string How to convert string to date to string in Swift iOS? Convert time.Time to string TypeError: a bytes-like object is required, not 'str' when writing to a file in Python3 How can I capitalize the first letter of each word in a string using JavaScript? Best way to verify string is empty or null Hive cast string to date dd-MM-yyyy Check for special characters in string How to convert any Object to String? Print "\n" or newline characters as part of the output on terminal Set the maximum character length of a UITextField in Swift How do I convert a Python 3 byte-string variable into a regular string? What does $ mean before a string? Delete the last two characters of the String Splitting a string into separate variables Figure out size of UILabel based on String in Swift Matching strings with wildcard How do I concatenate strings? Print very long string completely in pandas dataframe Check string for nil & empty Convert float to string with precision & number of decimal digits specified? How do I print my Java object without getting "SomeType@2f92e0f4"? enum to string in modern C++11 / C++14 / C++17 and future C++20 How should I remove all the leading spaces from a string? - swift Convert array to JSON string in swift Swift extract regex matches Convert a file path to Uri in Android How would I get everything before a : in a string Python

Questions with date tag:

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer? Moment.js - How to convert date string into date? Extract Month and Year From Date in R #1292 - Incorrect date value: '0000-00-00' Extract year from date Error in MySQL when setting default value for DATE or DATETIME How to Extract Year from DATE in POSTGRESQL Format date as dd/MM/yyyy using pipes Moment.js - tomorrow, today and yesterday Can I use an HTML input type "date" to collect only a year? Formatting a Date String in React Native How to initialize a variable of date type in java? How to properly add 1 month from now to current date in moment.js How to convert dd/mm/yyyy string into JavaScript Date object? Get only records created today in laravel Add A Year To Today's Date LocalDate to java.util.Date and vice versa simplest conversion? PHP date time greater than today Convert String to Carbon Moment Js UTC to Local Time HTML Display Current date Hive cast string to date dd-MM-yyyy moment.js, how to get day of week number How to convert an Instant to a date format? Oracle SQL - DATE greater than statement Python - Get Yesterday's date as a string in YYYY-MM-DD format Get date from input form within PHP How to force the input date format to dd/mm/yyyy? Format date and Subtract days using Moment.js How to add minutes to current time in swift How to convert Moment.js date to users local timezone? How to compare LocalDate instances Java 8 Getting Current date, time , day in laravel How can I parse / create a date time stamp formatted with fractional seconds UTC timezone (ISO 8601, RFC 3339) in Swift? Moment.js get day name from date Get current date in DD-Mon-YYY format in JavaScript/Jquery Getting the difference between two Dates (months/days/hours/minutes/seconds) in Swift How to convert a date to milliseconds Using momentjs to convert date to epoch then back to date Parsing date string in Go Java 8: Difference between two LocalDateTime in multiple units

Questions with casting tag:

Subtracting 1 day from a timestamp date Cast object to interface in TypeScript TypeScript enum to object array Casting a number to a string in TypeScript Hive cast string to date dd-MM-yyyy Casting int to bool in C/C++ Swift double to string No function matches the given name and argument types C convert floating point to int PostgreSQL : cast string to date DD/MM/YYYY Convert Int to String in Swift Convert Float to Int in Swift PostgreSQL: ERROR: operator does not exist: integer = character varying What is the difference between up-casting and down-casting with respect to class variable How to fix Warning Illegal string offset in PHP How to cast the size_t to double or int C++ How to have Java method return generic list of any type? Convert String to Date in MS Access Query What are the rules for casting pointers in C? C++ convert from 1 char to string? casting int to char using C++ style casting Convert Unicode data to int in python SELECT CONVERT(VARCHAR(10), GETDATE(), 110) what is the meaning of 110 here? Change column type in pandas Casting LinkedHashMap to Complex Object @Value annotation type casting to Integer from String Cast int to varchar How to avoid warning when introducing NAs by coercion Cast Object to Generic Type for returning Casting string to enum Java converting Image to BufferedImage Import pandas dataframe column as string not int TypeScript or JavaScript type casting Change type of varchar field to integer: "cannot be cast automatically to type integer" Convert base class to derived class SQL error "ORA-01722: invalid number" How to cast Object to its actual type? Why do we assign a parent reference to the child object in Java? CAST to DECIMAL in MySQL How to cast int to enum in C++? typecast string to integer - Postgres T-sql - determine if value is integer How do I concatenate a boolean to a string in Python? How to cast/convert pointer to reference in C++ Convert INT to FLOAT in SQL Cast Double to Integer in Java Why cannot cast Integer to String in java? Why don't Java's +=, -=, *=, /= compound assignment operators require casting? Android, How can I Convert String to Date? How do I convert a string to a number in PHP?

Questions with hive tag:

select rows in sql with latest date for each ID repeated multiple times PySpark: withColumn() with two conditions and three outcomes java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient Hive cast string to date dd-MM-yyyy How to save DataFrame directly to Hive? How to calculate Date difference in Hive Select top 2 rows in Hive Just get column names from hive table Create hive table using "as select" or "like" and also specify delimiter Hive Alter table change Column Name Difference between INNER JOIN and LEFT SEMI JOIN Hive: Filtering Data between Specified Dates when Date is a String How to Access Hive via Python? Add a column in a table in HIVE QL Hadoop/Hive : Loading data from .csv on a local machine What is the difference between partitioning and bucketing a table in Hive ? Hive ParseException - cannot recognize input near 'end' 'string' Handling NULL values in Hive How to get/generate the create statement for an existing hive table? How do I output the results of a HiveQL query to CSV? how to replace characters in hive? How to select current date in Hive SQL How to delete and update a record in Hive Hive insert query like SQL How to export a Hive table into a CSV file? Difference between Hive internal tables and external tables? Insert data into hive table Hive External Table Skip First Row Hive: how to show all partitions of a table? How to delete/truncate tables from Hadoop-Hive? Hive query output to file How to load a text file into a Hive table stored as sequence files When to use Hadoop, HBase, Hive and Pig? How to Update/Drop a Hive Partition? Hive load CSV with commas in quoted fields COALESCE with Hive SQL I have created a table in hive, I would like to know which directory my table is created in? How to set variables in HIVE scripts Hive: Convert String to Integer How to know Hive and Hadoop versions from command prompt? What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask Inserting Data into Hive Table How to load data to hive from HDFS without removing the source file? Where does Hive store files in HDFS? Does Hive have a String split function? Difference between Pig and Hive? Why have both?