Multiple Date format Same field - Qlik Sense

[GaryMcDonald]

Hello,

I cant seem to find a query that will resolve my issue, if anyone has any suggestions I would be very grateful.

I have a date field ([Effective Date]) where the format appears to change between 'dd/mm/yyyy' & 'dd/mm/yyyy hh:mm'.

When I load the data only the dates formatted with a timestamp appear - any suggestions as to how I can get all dates to appear?

an example attached

Many thanks

Gary

[jwjackso · Accepted solution]

I loaded your sample data and noticed that the timestamps are loading as strings and the dates as dates.  I used the Alt() function to convert the Effective Date field to a date.

Date(Alt(Date#([Effective Date],'M/DD/YYYY'),Date#([Effective Date],'M/DD/YYYY m:ss')),'MM/DD/YYYY') as [Effective Date]

[GaryMcDonald]

Thanks for this - Yes, the dates were mixed between string and numeric. Thanks 

[ManojMJ]

@GaryMcDonald when I load the "Copy of Date Format examples.xlsx then I found that Qlik has already been interpreted all the field values(Timestamps) of three date fields as Timestamps. So to convert these timestamps into Date, I simply used Date() function as it will format all timestamps into Date in specified Date Formats. Screenshots have been enclosed for your reference. If you have some other sample data where you were facing such issue then you could share that also.