Is there a simpler and faster way to transform dates stored as integers to dates?

[NenadV]

After one acquisition I've realized that dates in source SQL tables is, for unknown reason to me, stored as integer using this strange format.

For exmple: 1240305 -->2024-03-05 (YYYY-MM-DD)

I've found a  way:

tmpInvoices:

load *,

date(date#(tmpInvDate, 'YYYY-MM-DD')) as InvDate;

sql select cast(concat('20', substring(ltrim(str(DTIN65)),2,2), substring(ltrim(str(DTIN65)),4,2),right(ltrim(str(DTIN65)),2)) as date) tmpInvDate

from Invoices;

but some times, again for unknow reason to me it ends with this error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

[David_Friend]

Did you already review this old post: https://community.qlik.com/t5/QlikView-App-Dev/SQL-DateTime-conversion-failing/td-p/607474

[NenadV]

Thanks David. I did it now, but as expected it doesn't work well because of this strange and illogical field type used (int instead of date)

You have maybe overseen the example I gave.

Original and unexpected value stored in the table is 1240305 an it should be transformed to 2024-03-05 (YYYY-MM-DD).

[marcus_sommer]

Especially if you applies a preceding load to transform data you don't need to apply any transformation within the sql - just pull the data purely and then adjust them in Qlik.

[NenadV]

Thanks Marcus. that was the original question "Is there  a simpler way to transform those values stored as integers to dates?"

[marcus_sommer]

As far as the origin data have a common date structure they could be converted per date#(). It might be necessary to adjust the interpretation variables for it respectively to apply a changed version but in most cases it's not necessary and you may directly use:

date(date#(240305, 'YYMMDD'))

If there are various types of dates it might be wrapped by an alt() function to apply multiple different conversions.

[NenadV]

124 in 1240307 might be a year if that ERP system uses a RDBMS where 1.1.1900 is the first year.

In that case, 1900 +124 = 2024 and then maybe there is a simpler and faster way.

If not, I would dare to state there is no simpler way to the one I have come up to.

Thank you

[marcus_sommer · Accepted solution]

Such logic could be easily adjusted, like:

date(date#(1240307 + 19000000, 'YYYYMMDD'))

[NenadV]

Brilliant! Thank you Marcus.