topic Re: date format issue in QlikView

date format issue

Fri, 07 Aug 2015 07:10:30 GMT

Hi,

I have some dates in an xml file which are in the format d.m.yyyy hh:mm:ss. An example date is 1.6.2015 21:13:54. Now I want to show this date in the default format of my Qlikview document so that I have converted them like this in the Qlikview script

Date#(StartedAt,'dd.mm.yyyy hh:mm:ss') as testdate1,
date(Date#(StartedAt,'dd.mm.yyyy hh:mm:ss')) as testdate2,

This first one is without casting to date, and the second one is to cast it into local document format. However, when I check the document, they don't seem to be converted correctly. For example, please see the attached image

Can someone please tell me how to correct covert them into local document date format

Regards

Shah

Re: date format issue

sunny_talwar — Fri, 07 Aug 2015 07:13:48 GMT

Try this:

Date(Date#(Trim(StartedAt), 'dd.mm.yyyy hh:mm:ss')) as testdate3,

Date(Floor(Date#(Trim(StartedAt), 'dd.mm.yyyy hh:mm:ss'))) as testdate4

Re: date format issue

sunny_talwar — Fri, 07 Aug 2015 07:14:54 GMT

Here testdate3 will be still have the timestamp, but will only show Date. And testdate4 will only be Date (time floored to 0).

HTH

Best,

Sunny

Re: date format issue

Fri, 07 Aug 2015 07:15:30 GMT

already tried this

Date#(trim(StartedAt),'dd.mm.yyyy hh:mm:ss') as testdate1,
date(Date#(trim(StartedAt),'dd.mm.yyyy h:m:s')) as testdate2,

Re: date format issue

Fri, 07 Aug 2015 07:17:30 GMT

The problem is that I need to convert them to default document timestamp without flooring the time part as I need it in timestamp format of the document default

Re: date format issue

sunny_talwar — Fri, 07 Aug 2015 07:18:02 GMT

What I suggested is different

Your:

Date#(trim(StartedAt),'dd.mm.yyyy hh:mm:ss') as testdate1,

date(Date#(trim(StartedAt),'dd.mm.yyyy h:m:s')) as testdate2,

vs. Mine:

Date(Date#(Trim(StartedAt), 'dd.mm.yyyy hh:mm:ss')) as testdate3,

Date(Floor(Date#(Trim(StartedAt), 'dd.mm.yyyy hh:mm:ss'))) as testdate4

Re: date format issue

sunny_talwar — Fri, 07 Aug 2015 07:18:50 GMT

Try this:

TimeStamp(TimeStamp#(Trim(StartedAt), 'dd.mm.yyyy hh:mm:ss')) as testdate5,

Re: date format issue

Fri, 07 Aug 2015 07:19:19 GMT

In any case, it is still returning the same result even after flooring the time part as you suggested. I already tried trimming the string

Re: date format issue

antoniotiman — Fri, 07 Aug 2015 07:20:16 GMT

try' DD.MM.YYYY hh:mm:ss'

mm is minutes

MM is Month

Re: date format issue

Fri, 07 Aug 2015 07:20:37 GMT

yup, still no difference in the result

Re: date format issue

Kushal_Chawda — Fri, 07 Aug 2015 07:24:47 GMT

set variable in script

SET TimestampFormat='DD.MM.YYYY hh:mm:ss';

Now load the field as

=timestamp(timestamp#(StartedAt,'DD.MM.YYYY hh:mm:ss'))

Re: date format issue

Fri, 07 Aug 2015 07:24:49 GMT

That seems to have done the trick. Thanks Antonio and Sunny

Re: date format issue

antoniotiman — Fri, 07 Aug 2015 07:43:31 GMT

Hi,

However, whe You have Timestamp Format it is advisable to use Floor() function to extract Date

e.g.

=Date(Floor(TimeStamp#('1.6.2015 12:01:01','DD.MM.YYYY hh:mm:ss')))

because this doesn't need Windows Apis

Regards,

Antonio