
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Multiple Date format Same field - Qlik Sense
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
- Tags:
- date
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for this - Yes, the dates were mixed between string and numeric. Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
