question

PoelvanderRERon-6976 avatar image
1 Vote"
PoelvanderRERon-6976 asked PoelvanderRERon-6976 commented

Difference in milliseconds

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

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SaurabhSharma-msft avatar image
0 Votes"
SaurabhSharma-msft answered SaurabhSharma-msft edited

Hi @poelvanderreron-6976,

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
85133-image.png

Used Derived Column expression for testing
85151-image.png

Result
85090-image.png


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






image.png (12.7 KiB)
image.png (20.8 KiB)
image.png (6.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PoelvanderRERon-6976 avatar image
0 Votes"
PoelvanderRERon-6976 answered PoelvanderRERon-6976 commented

Hi @SaurabhSharma-msft

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


85149-knipsel2.jpg



Regards
Ron


knipsel2.jpg (158.0 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This appears to be a UI bug in data preview. Ron, do you mind filing a ticket for this bug from the Azure portal? I'll also open an internal tracker based on the similar condition you saw with Cached Lookup. Thank you!! Best, Mark

1 Vote 1 ·

Thanks @MarkKromer-8019 for the acknowledgement.

0 Votes 0 ·

I will giev it a try @MarkKromer-8019 . Never done that before :)

0 Votes 0 ·