TicksToDateTime Not Working

[MarkTeviotdale]

We are currently connected to Cosmos DB Databases with Table API using a SQL connector in PowerBI

Everything has been working just fine for a number of years now,  but overnight we ran into an issue with a TicksToDateTime conversion.

Here is a simplified example of query we are using

SELECT {
"PartitionKey": c["$pk"],
"RowKey": c["$id"],
"CreatedTStamp": TicksToDateTime(StringToNumber(c["CreatedTStamp"]["$v"]) - 621355968000070000),
"Animal": c["Animal"]["$v"]
}
AS Entities
FROM EventData c
WHERE c["$pk"] = "WeightRecord"

The "CreatedTStamp" Field no longer returns a value.

"CreatedTStamp": TicksToDateTime(StringToNumber(c["CreatedTStamp"]["$v"]) - 621355968000070000),

As mentioned this has been working for years,  We can strip the "CreatedTStamp" field request down to component parts and return the correct values,  however the last step in the request TicksToDateTime has ceased to work.  

The Table API stores the DateTime as Ticks from 1 AD at its most base level,  we are using this formula to covert it into a usable datetime at our end.

Can you please advise what has change overnight,  if we were informed of the change,  if there is a possibility the change is reverted and/or if you have any advise on how to proceed.

[Anonymous]

Hi @MarkTeviotdale ,

Can you please share some more detail information about this issue and error messages? They should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

In addition, have you check on the data source to confirm if this issue only appeared on power bi side?
If that is the case, I'd like to suggest you check on the recently updated if anything change applied to the api interface.

Regards,

Xiaoxin Sheng

[MarkTeviotdale · Accepted solution]

Thanks @Anonymous for your message. 

We have a ticket in with Azure and the issue appears to be on the Azure Cosmos side, the current workaround at the moment is to add a ltrim example below: 

add ltrim for leading zeros

SELECT TicksToDateTime(StringToNumber(ltrim("00638435451214931790", "0")) - 621355968000070000)