Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dsdeepak284
New Contributor

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

Re: How to change date format

Try this

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

sureshqv
Esteemed Contributor III

Re: How to change date format

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
New Contributor

Re: How to change date format

Not Works

Re: How to change date format

okay

hbandari3636
New Contributor II

Re: How to change date format

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
Contributor III

Re: How to change date format

hehehee

What do you mean not work???

Solution suggested by sunny must work...

Regards,

zebhashmi
Valued Contributor

Re: How to change date format

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


Re: How to change date format

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

cristianozilz
New Contributor III

Re: How to change date format

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.

Community Browser