Convert Hive TimeStamp in Date and Time at Qlikview

[#1]

Hi experts,
following data set

I've the following data set on Qlikview:

I'm trying to convert the Date_ID (that is a TimeStamp in Hive) in order to obtain the field Date and Time! I already try the Date# function but it still gives me this error:
Error while compiling statement: FAILED: ParseException line 1:11 cannot recognize input near 'top' '10' 'Date' in selection target'. Qlikview

How can I extract the Date and Time field from my Date_ID?

Many thanks!

[#2 · Accepted solution]

maybe:

=date(Date#('9-12-2013 03:00:10','DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY hh:mm:ss')

[#3]

Hi Rodrigo, thanks for your answer.

It still gives me error:

'Error while compiling statement: FAILED: ParseException line 1:11 cannot recognize input near 'top' '10' 'date' in selection target'.

[#4]

all date its this format?

have text on field?

[#5]

Yes, all the values follow the same format...

I'm trying with this script:

teste:

SQL SELECT date(Date#(Date_Id,'DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY hh:mm:ss')

FROM HIVE."default"."mytable";

[#6]

this canot use on SQL...

folow:

load  date(Date#(Date_Id,'DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY hh:mm:ss') as  Date_Id:

SQL SELECT Date_Id

FROM HIVE."default"."mytable";

[kkkumar82]

There your are ,

Please use the formatting in Load script not in SQL select.

Use "Preceding Load" option while your are selecting tables in edit script window and use the above formatting to the column in the Load statement.

[trdandamudi]

Please see the attached file and hope this helps:

Data:

LOAD *,

date(Date#(DateTime,'DD-MM-YYYY hh:mm:ss'),'DD/MM/YYY') as Date1,

Time(Time#(DateTime,'DD-MM-YYYY hh:mm:ss'),'hh:mm:ss') as Time1;

LOAD * Inline

[

DateTime

9-12-2013 03:00:00

];