40

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')
pele88
  • 802
  • 2
  • 8
  • 16
  • 3
    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. – Samson Scharfrichter Sep 09 '15 at 09:23

6 Answers6

63

try:

from_unixtime(unix_timestamp('12-03-2010' , 'dd-MM-yyyy'))
pjames
  • 192
  • 1
  • 4
  • 13
Ardit
  • 1,522
  • 17
  • 23
  • 48
    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)` – Samson Scharfrichter Sep 09 '15 at 09:36
  • 2
    And yes, I wish there was a better way to manipulate dates in Hive :-/ – Samson Scharfrichter Sep 09 '15 at 09:37
  • 1
    @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? – Mr Moose Jan 30 '19 at 18:38
  • @MrMoose, the obvious answer is: RTFM https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions – Samson Scharfrichter Jan 31 '19 at 12:16
  • @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. – Mr Moose Jan 31 '19 at 15:03
  • painful all these things – thebluephantom Sep 03 '20 at 13:59
54

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
KeyMaker00
  • 6,194
  • 2
  • 50
  • 49
5

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?

Samson Scharfrichter
  • 8,884
  • 1
  • 17
  • 36
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';
Terminator17
  • 782
  • 1
  • 6
  • 13
0

This will convert the whole column:

select from_unixtime(unix_timestamp(transaction_date,'yyyyMMdd')) from table1
cela
  • 2,352
  • 3
  • 21
  • 43
Bhargav Shah
  • 36
  • 1
0

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

pronay
  • 1