Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
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
Partner

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.