Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
GaryMcDonald
Contributor II

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

Labels (1)
1 Solution

Accepted Solutions
jwjackso
Specialist III

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]

View solution in original post

3 Replies
jwjackso
Specialist III

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
Contributor II
Author

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

ManojMJ
Partner - Contributor

@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.

ManojMJ_0-1744022516779.pngManojMJ_1-1744022553409.png