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