Adding datetime as dynamic content to a query

Question

This is my query
SELECT Id, ...
FROM Task
WHERE LastModifiedDate > @{activity('Lookup_SF_Task').output.firstRow.LastExtractRecordDate}

This is how LastExtractRecordDate is being pulled by a Look Up module.

This is the error I get

No matter how I wrap/format/cast/convert the date it seems to find a way to complain..

Can anyone explain how to use dynamic content / parameters / syntax ?

Thank you.

Answer 1

Hi @Bakar, Cem ,

If you add single quote in the existing query, it should work e.g.,

SELECT Id, ... FROM Task WHERE LastModifiedDate > '@{activity('Lookup1').output.firstRow.LastExtractRecordDate}'   

You can also do datetime formatting e.g.,

@concat('SELECT Id, ... FROM Task WHERE LastModifiedDate >''' , formatDateTime(activity('Lookup1').output.firstRow.LastExtractRecordDate, 'yyyy-MM-dd'), ''' ')  

Hope this helps! Thanks! :)

Answer 2

@Bakar, Cem
@Nasreen Akter 's idea was my first thought also.

So you are saying that the inline value should be datetime rather than a string representing a datetime?
In that case remove the {curly braces}. The {curly braces} after @, mean "Turn the result of the expression into a string".
Another possibility is re-adding the zone.

In any case, we should all take a look at this example.

When you specify the SOQL or SQL query, pay attention to the DateTime format difference. For example:

SOQL sample: SELECT Id, Name, BillingCity FROM Account WHERE LastModifiedDate >= @{formatDateTime(pipeline().parameters.StartTime,'yyyy-MM-ddTHH:mm:ssZ')} AND LastModifiedDate < @{formatDateTime(pipeline().parameters.EndTime,'yyyy-MM-ddTHH:mm:ssZ')}
SQL sample: SELECT * FROM Account WHERE LastModifiedDate >= {ts'@{formatDateTime(pipeline().parameters.StartTime,'yyyy-MM-dd HH:mm:ss')}'} AND LastModifiedDate < {ts'@{formatDateTime(pipeline().parameters.EndTime,'yyyy-MM-dd HH:mm:ss')}'}

Answer 3

It is always the last spot you look. Salesforce requires the date formatted in specific format. Including the zone.
Without curly braces and adding time zone did the trick.

Thank you all for your timely help!

Answer 4

How did you define this query? If you see a “Manage Parameters” window — https://learn.microsoft.com/en-us/power-query/power-query-query-parameters — then try changing the type of parameter to “Date/Time”.

Answer 5

Thank you for both answering so quickly.
@Viorel - The field type is defined as datetime2(0) - without any filter it comes back from AzureSQL like this "LastExtractRecordDate": "2021-04-08T23:59:14Z"
However, at the sql table you do not see the zone details so it looks like this - 2021-04-09 02:05:09

@Nasreen Akter ,
I have tried both but regardless of how the date is presented I get the following error.