0

I have a source table click with a column named click_date whose datatype is varchar, it contains values with two different format of dates in the same column. For example:

Jul 17 2018 4:54PM
2019-02-05 08:20:29.000

I have a target table named click and I need to map the data into it in a column named click_date whose datatype is bigint.

So it is throwing an error while doing casting

Tried the below :

td_time_parse(cast(cast(click_date as timestamp)as varchar))

But it doesn't solve both the formats.. I need to convert both the date formats into integer so that it can be loaded into the target.

note that target data type cannot be changed and it is bigint. Фny leads will be appreciated.

Pijush Kanti Das
  • 1
  • 1
  • 5

2 Answers2

0

You could use a combination of date_parse and to_unixtime to convert your date in varchar into a unixtimestamp (which is in double datatype).

for date format: 2019-02-05 08:20:29.000

to_unixtime(date_parse('2019-02-05 08:20:29.000','%Y-%m-%d %h:%i:%s'))

for date format: Jul 17 2018

to_unixtime(date_parse('Jul 17 2018','%b %d %Y'))

This will return you a double datatype of your date which you can store it in your database.

Rishu Shrivastava
  • 3,745
  • 1
  • 20
  • 41
  • Hi Rishu , actually the other format was a typo.. it is like: this May 26 2017 4:57PM , so the above logic is not working . Can u pls help with the updated one .. also for the first format for . 000 it is throwing error – Pijush Kanti Das Jun 29 '20 at 05:30
0

You can use below query to handle both formats of dates with one expression. It essentially looks if the date/timestamp starts with a numeric or not.

select 
if(click_date rlike '^[0-9]', unix_timestamp(click_date ,'yyyy-MM-dd HH:mm:ss'), unix_timestamp(click_date,'MMM dd yyyy')) as click_date_mod 
from click;
Prabhat Ratnala
  • 650
  • 5
  • 17
  • Thanks Prabhat for the help. But these functions doesn't work in presto.. Can you please help if possible to do the same which works in presto engine. – Pijush Kanti Das Jun 29 '20 at 04:48