CODEWITHSUNDEEP
sqlhadoopimpala
phalondon
phalondon

Reputation: 305

How to convert this String to Timestamp

i have a column with the Data like this "08.06.2017/10:20:46". Data Type is string. i want to convert it into timestamp. i tried CAST("08.06.2017/10:20:46" AS TIMESTAMP) but it doesn't work Can you please help me to convert it?? Thanks

Upvotes: 5

Views: 125013

Answers (3)

Ramesh
Ramesh

Reputation: 1484

Hive and Impala deals with dates in a similar manner. You need to use the function unix_timestamp to accept non standard date formats, then use from_unixtime function to convert to a timestamp. Below code should work for your example. 

SELECT from_unixtime(unix_timestamp('08.06.2017/10:20:46','dd.MM.yyyy/hh:mm:ss'));

Upvotes: 1

D Ie
D Ie

Reputation: 841

For mysql, there is a function called STR_TO_DATE

You should call it like this:

STR_TO_DATE(string , format)

Take a look at Format specifiers

In your case I'd try with

STR_TO_DATE('08.06.2017/10:20:46','%d.%m.%Y/%H:%i:%s')

Edit: sorry about the mysql stuff, don't know if I'm supposed to remove it or not... Anyways, for impala, this could get you started:

cast(unix_timestamp('08.06.2017/10:20:46', "dd.MM.yyyy/HH:mm:ss") as timestamp)

The casting is because unix_timestamp function returns a bigint (take a look here for more information about the impala datetime functions)

Upvotes: 5

praveen kumar
praveen kumar

Reputation: 13

For Oracle SQL,

use to_date function as below,

SELECT TO_DATE('08.06.2017/10:20:46','dd.mm.yyyy/hh:mi:ss') FROM DUAL;

This conveys that the string is in this(dd.mm.yyyy/hh:mi:ss) form of date and convert to date column...

Upvotes: 1

Related Questions