topic Re: How to get this date stored as text as a regular date field in QlikView? in QlikView

How to get this date stored as text as a regular date field in QlikView?

NenadV — Fri, 14 Aug 2026 10:46:34 GMT

Hi,

attached is the data source file from which I can't get a regular date field from the DateDMMMYYYY column in QlikView using this standard function or even date#():

date(DateDMMYYYY,'d.MM.YYYY') as DateProduced;

Any help is very welcome.

Thank you

Re: How to get this date stored as text as a regular date field in QlikView?

lennart_mo — Fri, 14 Aug 2026 11:38:39 GMT

Hi @NenadV,

Going off your sample data I would suggest the following formula:

date(Alt(date#(DateDMMYYYY, 'DDMMYYYY'), date#(DateDMMYYYY, 'DMMYYYY'))) as DateProduced

The date#() function needs the format used in your dataset, the date() function converts that to an actual date, corresponding to the format you set locally.

I used the Alt() as QlikSense seemed to have a problem with the switch between one and two days in the string, I assume so does QlikView. So if there's a non-numeric value returned from an 8 digit date, Qlik automatically resorts to the next format resolving a 7 digit date. You could also solve this by checking the length of the string.

Best regards

Lennart

Re: How to get this date stored as text as a regular date field in QlikView?

NenadV — Fri, 14 Aug 2026 12:06:05 GMT

Thanks Lennart,

I didn't know about the Alt() function.

Your Formula doesn't work because it ends with this date 30.12.1899 for all values

Re: How to get this date stored as text as a regular date field in QlikView?

NenadV — Fri, 14 Aug 2026 12:22:42 GMT

But this formula does work well for both dates, with one digit day and two digits day:

if(len(rtrim(DateDMMYYYY)) = 7, date(Alt(date#(DateDMMYYYY, 'DMMYYYY'), date#(DateDMMYYYY, 'DMMYYYY'))), date(Alt(date#(DateDMMYYYY, 'DDMMYYYY'), date#(DateDMMYYYY, 'DDMMYYYY')))) as DateProduced,

So, without your valuable input I wouldn't have solved it.

Thank you, sir

Re: How to get this date stored as text as a regular date field in QlikView?

lennart_mo — Fri, 14 Aug 2026 13:00:47 GMT

Okay, glad you got it!

Don't know why my formula seemed to return all zeros (the numeric value of 30.12.1899) for you, but maybe that's just a difference between Sense and View. Do you use NullAsValue by any chance?

With your new formula you should be able to drop the Alt() though, reducing the total length by quite a bit:

if(len(rtrim(DateDMMYYYY)) = 7, date(date#(DateDMMYYYY, 'DMMYYYY')), date(date#(DateDMMYYYY, 'DDMMYYYY'))) as DateProduced