Hive cast string to date dd-MM-yyyy

Question

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')

Charleencharlemagne

There is no such thing as a "date type with specific format". Type DATE is stored as binary, and displayed by default in ISO format; if you want to display it in another format, you must format it explicitly, on a case-by-case basis. – Equate

Answer 1

try:

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

Canicula

That's still a String, with TimeStamp format. To cast it to a Date you must wrap it in cast(to_date(from_unixtime(unix_timestamp(STR_DMY, 'dd-MM-yyyy'))) as date) – Equate

And yes, I wish there was a better way to manipulate dates in Hive :-/ – Equate

@SamsonScharfrichter, this is quite an old post now, are there new and improved ways to manipluate dates in Hive that you are now aware of? – Boy

@MrMoose, the obvious answer is: RTFM cwiki.apache.org/confluence/display/Hive/… – Equate

@SamsonScharfrichter, Thanks! That is a good response, and I will take a look. I am new to Hive and was hoping to be spoon fed :) I know that isn't the right expectation though. I thought that I could now do without the cast as the docs seem to suggest that, post 2.1.0 release, return type of to_date is now date...that doesn't seem to be the case with what I have though. So for me, still no better way to manipulate dates given I'm just trying to cast a string with a fixed format to a date. – Boy

painful all these things – Puzzler

Answer 2

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

Copyboy

Answer 3

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), '-',
            substr(STR_DMY,4,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?

Equate

Answer 4

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
where 
date_Format(birth_day, 'yyyy-MM-dd') = '2019-04-16';

Omora

Answer 5

This will convert the whole column:

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

Transcript

Answer 6

Simpler way select cast("07/15/2022" as date format "MM/DD/YYYY") from dual Below is a link to the wiki https://cwiki.apache.org/confluence/display/Hive/CAST...FORMAT+with+SQL%3A2016+datetime+formats

Enroll