topic Re: Date# Formating conversion issue? in QlikView

Date# Formating conversion issue?

Tue, 05 Feb 2013 17:07:43 GMT

Im trying to format my date to look like this: ,'DD/MM/YYYY'

I thought my input mask was correct, but it won't work. Please help

=Date(Date#(ActualStart,'YYYY-MM-DD h:mm:ss TT'),'DD/MM/YYYY')

Raw Input Dates

2012-12-12T17:00:00

2012-12-14T17:00:00

2012-12-20T17:00:00

2012-12-21T17:00:00

2013-01-02T08:00:00

2013-01-02T17:00:00

2013-01-08T17:00:00

2013-01-18T17:00:00

2013-01-21T17:00:00

2013-02-25T17:00:00

2013-02-27T17:00:00

2013-03-01T17:00:00

2013-03-05T08:00:00

2013-03-05T17:00:00

2013-03-06T17:00:00

2013-03-07T17:00:00

2013-03-11T17:00:00

2013-03-21T17:00:00

Re: Date# Formating conversion issue?

rbecher — Tue, 05 Feb 2013 17:56:54 GMT

Hi Brain,

first replace the T between date and time and then use the mask 'YYYY-MM-DD hh:mm:ss'.

- Ralf

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 18:06:05 GMT

I tried this but no luck: I inserted the 'T' to match the input date .... 2013-03-21T17:00:00

=Date(Date#(ActualStart,'YYYY-MM-DDThh:mm:ss'),'DD/MM/YYYY')

Re: Date# Formating conversion issue?

chematos — Tue, 05 Feb 2013 18:10:54 GMT

You should do this in load script.

=Date(Date#((Replace(ActualStart,'T',' '),'YYYY-MM-DD h:mm:ss TT') ,'DD/MM/YYYY')

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 18:27:31 GMT

Good idea. I was totally expecting this too work. It rendered it null? Must be input mask?

Date(Date#(Replace(ActualStart,'T',' '),'YYYY-MM-DD h:mm:ss TT') ,'DD/MM/YYYY') as ActualStart,

Re: Date# Formating conversion issue?

rbecher — Tue, 05 Feb 2013 18:32:57 GMT

Why the TT?

Date(Date#(Replace(ActualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY') as ActualStart,

Re: Date# Formating conversion issue?

chematos — Tue, 05 Feb 2013 18:34:07 GMT

Right, delete TT ...

While you are loading the table at the script, use the expression above to format ActualStart field, you can use the same name or duplicate the field renaming with other name field and keeping the original if you want to compare both of them to see if the result is ok.

Hope this helps.

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 18:36:10 GMT

This portion works. But the Date() wont....

Date#(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss TT')

Re: Date# Formating conversion issue?

chematos — Tue, 05 Feb 2013 18:40:54 GMT

Try:

Date(Date(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY')

Maybe this works:

Date(Replace(ManualStart,'T',' '),'DD/MM/YYYY')

or:

Date#(Date#(Replace(ManualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') ,'DD/MM/YYYY')

I'm not sure what's the result using Date# or Date, sometimes works in a different and unexpected way...

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 18:41:29 GMT

I copied it from the Default mask but your right no need for it. I removed after the fact.....

Re: Date# Formating conversion issue?

Anonymous — Tue, 05 Feb 2013 18:45:41 GMT

Hi,

can you please send test application.

Regards

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 18:56:22 GMT

This worked to resolve it on object level while the Script stripped out the 'T' so the Date function works.

Thank You Both for your expertise.

Script

Date#(Replace(ActualStart,'T',' '),'YYYY-MM-DD hh:mm:ss') as ActualStart,

Object

Date(ManualStart,'DD/MM/YYYY')

Re: Date# Formating conversion issue?

Tue, 05 Feb 2013 19:01:11 GMT

It can be combined as one in Script, as Ralph mentioned. the 'TT' i copied from the default mask messed it up. but the key was removing the 'T' from the date. Dont know why MS project inserts in the first place.

Re: Date# Formating conversion issue?

rbecher — Tue, 05 Feb 2013 19:06:08 GMT

I find this T very often in XML timestamp sources. Maybe it's a standard..

- Ralf

Re: Date# Formating conversion issue?

Wed, 22 May 2013 18:08:12 GMT

I am trying to get the date and time with AM PM from below ' 2010-11-02T08:42:37.6900423-07:00'

which is 2010-11-02 8:42:37 AM, can you please help

=TIMESTAMP#(Date#(Replace('2010-11-02T08:42:37.6900423-07:00','T',' '),'YYYY-MM-DDhh:mm:ss.[fff]') ,'YYYY-MM-DDhh:mm:ss TT')

Re: Date# Formating conversion issue?

Wed, 22 May 2013 18:08:44 GMT

I am trying to get the date and time with AM PM from below ' 2010-11-02T08:42:37.6900423-07:00'

which is 2010-11-02 8:42:37 AM, can you please help

=TIMESTAMP#(Date#(Replace('2010-11-02T08:42:37.6900423-07:00','T',' '),'YYYY-MM-DDhh:mm:ss.[fff]') ,'YYYY-MM-DDhh:mm:ss TT')