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

User · Answer

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

User · Answer

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

User · Answer

This will convert the whole column   select from unixtime unix timestamp transaction date  yyyyMMdd    from table1

User · Answer

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

User · Answer

try   from unixtime unix timestamp  12-03-2010     dd-MM-yyyy

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

Examples related to string

Examples related to date

Examples related to casting

Examples related to hive