Difference in milliseconds

Question

Hi

we have a SQL DB containing two fields, both defined as datetime2

fieldA 2020-10-20 07:25:58.4543440
fieldB 2020-10-20 07:25:58.4658980

We need to know the difference in milliseconds between fieldA and fieldB.

This doesn't work:
millisecond(fieldB , 'yyyy-MM-dd HH:mm:ss.SSSSSSS') - millisecond(fieldA, 'yyyy-MM-dd HH:mm:ss.SSSSSSS')

I get some strange error in the expression builder:

{"value":"11","type":"LosslessNumber","isLosslessNumber":true}

Anybody knows what I am doing wrong??
Tried other options, but don't get it working.

By the way, an additional requirement: If one of the fields contains a NULL value, the outcome must be 0.

regards
Ron

@HarithaMaddi-MSFT
@MartinJaffer-MSFT

Answer 1

Hi @Poel van der, RE (Ron) ,

Thanks for using Microsoft Q&A !!
Can you please pass only the column name to the function as per the documentation, as second parameter is a used for passing a timezone.
millisecond(fieldB) - millisecond(fieldA)

I have used it and it worked fine for me -
Source Table

**Used Derived Column expression for testing **

Result

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

Hi @Saurabh Sharma

It partially worked. The values 11 and 6 are correct, but why do I get all that surrounding text like ' {"value": ' etc. ?

Regards
Ron