Skip to main content
Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
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,

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

Astrato.io 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,

Astrato.io Head of R&D
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.....