How to change date format

[dsdeepak284]

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?

[sunny_talwar]

Try this

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

[Chanty4u]

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]

Not Works

[sunny_talwar]

okay

[hbandari3636]

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]

hehehee

What do you mean not work???

Solution suggested by sunny must work...

Regards,

[zebhashmi]

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]

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.