Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Date# Formating conversion issue?

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Date# Formating conversion issue?

Why the TT?

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

15 Replies
MVP
MVP

Re: Date# Formating conversion issue?

Hi Brain,

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

- Ralf

Not applicable

Re: Date# Formating conversion issue?

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')

chematos
Valued Contributor II

Re: Date# Formating conversion issue?

You should do this in load script.

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

Not applicable

Re: Date# Formating conversion issue?

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,

MVP
MVP

Re: Date# Formating conversion issue?

Why the TT?

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

chematos
Valued Contributor II

Re: Date# Formating conversion issue?

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.

Not applicable

Re: Date# Formating conversion issue?

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

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

chematos
Valued Contributor II

Re: Date# Formating conversion issue?

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...

Not applicable

Re: Date# Formating conversion issue?

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

Community Browser