Question: Here is a sample of my data in CSV format:

```
6/30/2014 23:57,2006,604,131
7/1/2014 0:24,2217,263,143
6/30/2014 23:26,173,3481,134
...

```

I would like to load this CSV file in Impala table and have the first column as "TIMESTAMP":

```
CREATE TABLE my_table(col1 timestamp, col2 int, col3 int, col4 int)
  row format delimited
  fields terminated by ',';

LOAD DATA INPATH '/dataset/data' INTO TABLE my_table;

```

But Impala accepts TIMESTAMPS as the following format: 

```
YYYY-MM-DD HH:MM:SS.sssssssss

```

While my data is: 

```
MM/DD/YYYY HH:MM 

```

How can I convert my data in the fastest way to be readable by Impala as TIMESTAMPS? I am trying to use `regexp_replace` but was not quite successful to make a good regular expression.

Answer: 
The from\_unixtime(unix\_timestamp( timestamp, 'input\_format' )) solution will work with Hive, but, for whatever reason, it does not work with Impala. I would consider this a bug and recommend you submit it to Cloudera.
> 
> I am trying to use regexp\_replace but was not quite successful to make
>  a good regular expression
> 
> 
> 

What about regular expressions to extract individiual fields from your ugly date format, then good old *printf* to rebuild a consistent format?

```
select printf('%04d-%02d-%02d %02d:%02d:%02d.%06d'
             , cast(regexp_extract('7/1/2014 0:24', '[0-9]+/[0-9]+/([0-9]+) [0-9]+:[0-9]+', 1) as int)
             , cast(regexp_extract('7/1/2014 0:24', '([0-9]+)/[0-9]+/[0-9]+ [0-9]+:[0-9]+', 1) as int)
             , cast(regexp_extract('7/1/2014 0:24', '[0-9]+/([0-9]+)/[0-9]+ [0-9]+:[0-9]+', 1) as int)
             , cast(regexp_extract('7/1/2014 0:24', '[0-9]+/[0-9]+/[0-9]+ ([0-9]+):[0-9]+', 1) as int)
             , cast(regexp_extract('7/1/2014 0:24', '[0-9]+/[0-9]+/[0-9]+ [0-9]+:([0-9]+)', 1) as int)
             , 0
             , 0 )
from DUAL limit 1

```

2014-07-01 00:24:00.000000