Show date from date field with different date formats

[#1]

Hi there i get every day a csv file that contains transaction logs from different databases. Because of this the date field has many different types of date formats in it. I include the date field in the attached excel file.

I want to display all of the different date formats that are include in the field, in the following format: e.g. 11/11/2012. ( 'DD/MM/YYYY').

Unfortunately the code i wrote applies to certain date formats, not to all of them.

Any help would be very appreciated.

[hic]

Timestamp(

Alt(

  Timestamp#([Timestamp],'DD/MM/YYYY hh:mm[:ss]'),

  Timestamp#([Timestamp],'DD-MM-YYYY hh:mm[:ss]')

  )

,'DD/MM/YYYY hh:mm') as Timestamp

or if you want it as a date:

Date(

floor(

  Alt(

   Timestamp#([Timestamp],'DD/MM/YYYY hh:mm[:ss]'),

   Timestamp#([Timestamp],'DD-MM-YYYY hh:mm[:ss]')

   )

  )

,'DD/MM/YYYY') as Date

In both cases, you can have a list of different date formats inside the Alt() function. QlikVIew will try the different formats, one after another.

HIC

[whiteline · Accepted solution]

This expression seems to load all correct as numeric timestamps:

alt(timestamp#([Last parameters download date/time], 'D/M/YYYY h:m'), timestamp#([Last parameters download date/time], 'D/M/YYYY h:m:s'))

Then you can use date()  function to change the format as you want:

date(alt(...),  'DD/MM/YYYY')

But if you want not just display date in that format but also discard time information, you should use floor() function before:

date(floor(alt(...)),  'DD/MM/YYYY')

[#4]

Thank you both for your quick responses!

You solved this problem for me!

Thank you again guys!!!

[#5]

SUper thanks!