topic Re: Convert TimeStamp format in QlikView

Convert TimeStamp format

Wed, 29 Oct 2014 13:37:52 GMT

Hello community,

how can I convert this TimeStamp format: '20141029122728' to this one: TimestampFormat='DD.MM.YYYY hh:mm:ss';

Thank you,

Thorsten

Re: Convert TimeStamp format

Anonymous — Wed, 29 Oct 2014 13:43:06 GMT

Try this

Date(Timestamp(Today()),'DD/MM/YYYY hh:mm:ss')

Instead of Today() use your dateformatte field

Re: Convert TimeStamp format

jyothish8807 — Wed, 29 Oct 2014 13:46:07 GMT

Hi Thorsten,

Try this:

Timestamp(Timestamp#(Yourfield,'YYYYMMDDhhmmss'),'DD.MM.YYYY hh:mm:ss') as NewTime

Regards

Re: Convert TimeStamp format

Colin-Albert — Wed, 29 Oct 2014 13:49:17 GMT

This will convert your date

date(date#( '20141029122728' , 'YYYYMMDDhhmmss'), 'DD.MM.YYYY hh:mm:ss')

date(date#(YourDateField, 'YYYYMMDDhhmmss'), 'DD.MM.YYYY hh:mm:ss')

Re: Convert TimeStamp format

lironbaram — Wed, 29 Oct 2014 13:51:01 GMT

hi,

here is a script for doing the conversion,

please note i assume that month and day will be given with two digits allways

load *,

Timestamp(MakeDate(left(Original,4),mid(Original,5,2),mid(Original,7,2))+mid(Original,9,2)/24+mid(Original,11,2)/1440+mid(Original,13,2)/86400,'DD.MM.YYYY hh:mm:ss') AS Calculated;

LOAD * INLINE [

Original

20141029122728

];

Re: Convert TimeStamp format

Colin-Albert — Wed, 29 Oct 2014 13:52:21 GMT

It would be better to split your timestamp filed into two separate date and time fields as this will use less memory in the app.

date(date#( '20141029122728' , 'YYYYMMDDhhmmss'), 'DD.MM.YYYY') as date

time(date#( '20141029122728' , 'YYYYMMDDhhmmss'), hh:mm:ss) as time

Also if you do not need the data in seconds, then just storing the time as hh.mm will further reduce the overhead.

Re: Convert TimeStamp format

Wed, 29 Oct 2014 14:54:05 GMT

Thank you all for showing various options.