topic Re: 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. in QlikView

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

NenadV — Thu, 07 Mar 2024 10:16:41 GMT

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.

Re: 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.

David_Friend — Wed, 06 Mar 2024 18:56:43 GMT

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

Re: 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.

NenadV — Thu, 07 Mar 2024 09:34:29 GMT

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

Re: 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.

marcus_sommer — Thu, 07 Mar 2024 10:06:53 GMT

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.

Re: 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.

NenadV — Thu, 07 Mar 2024 10:14:43 GMT

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

Re: 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.

marcus_sommer — Thu, 07 Mar 2024 10:29:03 GMT

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.

Re: 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.

NenadV — Thu, 07 Mar 2024 10:40:02 GMT

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

Re: 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.

marcus_sommer — Thu, 07 Mar 2024 11:06:07 GMT

Such logic could be easily adjusted, like:

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

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

NenadV — Thu, 07 Mar 2024 11:58:56 GMT

Brilliant! Thank you Marcus.