Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
dsdeepak284
Contributor II
Contributor II

How to change date format

DATE

Invoice Id

Jan 02 2018

12773uii

Feb 05 2018

22332

02-29-2017

123321

03-27-2018

9984

04-20-2018

ssdds8890

I have date in different formats, I wants the result in number format ex: (43210), I tried it in different ways

(Date#(Date("DATE",'DD/MM/YYYY')  and Date("DATE",'DD/MM/YYYY') but didn't got expected result.

Can anyone help me to get it?

9 Replies
sunny_talwar

Try this

Date(Alt(Num(Date#(DATE, 'MM-DD-YYYY')), Num(Date#(DATE, 'MMM DD YYYY'))), 'DD/MM/YYYY') as DATE

Chanty4u
MVP
MVP

try this

You can give in each format into below code and try

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' ) as newDate

dsdeepak284
Contributor II
Contributor II
Author

Not Works

sunny_talwar

okay

hbandari3636
Contributor III
Contributor III

You can try the same script as above with adding num function in the beginning to convert into num format.

num(alt( date#(date,'MMM DD YYYY'),

date#(date,'MM-DD-YYYY'),

'No valid date')) as newDate

or

if you want to convert everything into particular date format try

date(alt( date#(date,'MMM DD YYYY'),

date#(date,'MM-DD-YYYY'),

'No valid date'),'your date format') as newDate

big_dreams
Creator III
Creator III

hehehee

What do you mean not work???

Solution suggested by sunny must work...

Regards,

zebhashmi
Specialist
Specialist

As he is looking for number format

Date#(Alt(Num(Date#(DATE, 'MM-DD-YYYY')), Num(Date#(DATE, 'MMM DD YYYY'))), 'DD/MM/YYYY') as DATE


sunny_talwar

I think OP might have changed the requirement at a later time... initially he wanted the output in DD/MM/YYYY format...

cristianozilz
Partner - Contributor III
Partner - Contributor III

Hi,

I did Sunny Talwar example's and worked perfectly.

I Attached a app with this example.

I note that have a date "02-29-2017", but how february don't have 29 day the qlik changed to "03-01-2017".

Note i did two diferents data formats.

And i use floor command to convert to number.