Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rbecher
MVP
MVP

Why the TT?

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

Vizlib Head of R&D

View solution in original post

15 Replies
rbecher
MVP
MVP

Hi Brain,

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

- Ralf

Vizlib Head of R&D
Not applicable
Author

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
Specialist II
Specialist II

You should do this in load script.

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

Not applicable
Author

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,

rbecher
MVP
MVP

Why the TT?

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

Vizlib Head of R&D

View solution in original post

chematos
Specialist II
Specialist II

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
Author

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

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

chematos
Specialist II
Specialist II

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
Author

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