Hive date format matching

Question

How can i match particular date format in hive query, as i have to get those rows having date format other than max of rows.

Eg. My max of rows have date format as MM/dd/yyyy and i have to list all rows other than above format

+----------------------------+--------------------------+--------------------+-----------+
| AllocationActBankAccountID | GiftCardActBankAccountID | UpdateTimeStampUtc |   Date    |
+----------------------------+--------------------------+--------------------+-----------+
|                         14 |                       14 | 41:39.8            | 4/19/2016 |
|                         14 |                       14 | 37:16.4            | 4/20/2016 |
|                         14 |                       14 | 52:15.2            | 4/21/2016 |
|                         14 |                       14 | 52:15.2            | 2/11/2019 |
|                         14 |                       14 | 52:15.2            | 12-Feb-19 |*
|                         14 |                       14 | 41:39.8            | 2/13/2019 |
+----------------------------+--------------------------+--------------------+-----------+

I want to get * marked data (Date = 12-Feb-19)

Why are you using string with non ISO format and not date/timestamp types? — David דודו Markovitz, Apr 17 '17 at 09:51
I can't even start to describe how bad is what you are trying to do here. It's like the concept of integration never existed. — David דודו Markovitz, Apr 17 '17 at 10:00
What do u want to achieve? if u want parse multiple date formats then use UDF — Taha Naqvi, Apr 17 '17 at 10:00
I am just trying to have date pattern matching, if any date is not as per the mentioned format i can exclude or include from query — sunny rajpal, Apr 17 '17 at 10:19

David דודו Markovitz · Answer 1 · 2017-04-17T18:09:24.847

select  *
from    mytable
Where   date not rlike '^([1-9]|1[0-2])/([1-9]|[1-2][0-9]|3[0-1])/(19|20)\\d{2}$'

or

select  *
from    mytable
Where   not
        (
            date rlike '^\\d{1,2}/\\d{1,2}/\\d{4}$'
        and cast(split (date,'/')[0] as int) between 1 and 12
        and cast(split (date,'/')[1] as int) between 1 and 31 
        and cast(split (date,'/')[2] as int) between 1900 and 2099 
        )

or

select  date
from    mytable
Where   coalesce(from_unixtime(to_unix_timestamp(date,'M/d/y')),'0000-01-01') 
            not between date '1900-01-01' and date '2099-12-31'

Hive date format matching

1 Answers1