Inconsistent results with ConvertToLocalTime()

Oggy172 — Fri, 05 Dec 2025 09:27:23 GMT

Hi all,

I'm having issues surrounding ConvertToLocalTime and the UK's DST change.

For clarity, the change happened on 26th October 2025.

Our setup is QlikSense (Cloud) to Dynamics365 Business Central via Odata v4. I am trying to build some of these queries as incremental loads (Dump, save to disk & on the next run, pull anything >= last CreatedAt time)

I'm running this code, which isn't overly complex, to build an Exclusion List.

LIB CONNECT TO 'BC Testing - Odata:BC Odata Connection'; RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "Document_No", "SystemCreatedAt", "__FK_value" FROM "value" FK "__FK_value") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION( URL "$(vBaseURL)/$(vTable)?$filter=ManualDeleteReason eq 'Incorrect Order'", HTTPHEADER "Authorization" "Bearer $(vAccessToken)"); _ExclusionList: LOAD distinct [Document_No] as ExcludedOrders, SystemCreatedAt as SysCreatedOrig, date(Timestamp( ConvertToLocalTime( Timestamp#([SystemCreatedAt], 'YYYY-MM-DDTHH:mm:ss.fffZ'), 'Europe/London')),'DD/MM/YYYY hh:mm') as SystemCreatedAt RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_value]) order by SystemCreatedAt asc ;

and anything pre DST is converted, anything post, isn't.

I tried using ALT, but that made no difference.

LIB CONNECT TO 'BC Testing - Odata:BC Odata Connection'; RestConnectorMasterTable: SQL SELECT "__KEY_root", (SELECT "Document_No", "SystemCreatedAt", "__FK_value" FROM "value" FK "__FK_value") FROM JSON (wrap on) "root" PK "__KEY_root" WITH CONNECTION( URL "$(vBaseURL)/$(vTable)?$filter=ManualDeleteReason eq 'Incorrect Order'", HTTPHEADER "Authorization" "Bearer $(vAccessToken)"); _ExclusionList: LOAD distinct [Document_No] as ExcludedOrders, SystemCreatedAt as SysCreatedOrig, Date( ConvertToLocalTime( Alt( Timestamp#([SystemCreatedAt], 'YYYY-MM-DDThh:mm:ss[.fff]Z'), Timestamp#([SystemCreatedAt], 'YYYY-MM-DDThh:mm:ss[.fff]+00:00') ), 'Europe/London' ), 'DD/MM/YYYY hh:mm' ) as SystemCreatedAt RESIDENT RestConnectorMasterTable WHERE NOT IsNull([__FK_value]) ORDER BY SystemCreatedAt asc; drop table RestConnectorMasterTable; exit script;

If I use the same functions on a live table, not an archive (and so only records in the table are post DST) - the function works correctly.

Can anyone shed some insight? I cant afford to dump the whole archive over the API each time.

Re: Inconsistent results with ConvertToLocalTime()

rubenmarin — Fri, 05 Dec 2025 19:02:29 GMT

Hi, have you tried with only 'London'?, I have some apps with 'Madrid' for Spain area and noone has tagged some error (which doesn't means that it's correct, it might be than the users didn't care).

Also note that the invalid argument for the ignore_dst parameter is True, but if it's not especified, it's False: https://help.qlik.com/en-US/sense/November2025/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/converttolocaltime.htm

topic Inconsistent results with ConvertToLocalTime() in App Development

Inconsistent results with ConvertToLocalTime()

Re: Inconsistent results with ConvertToLocalTime()