question

cmrbkr avatar image
0 Votes"
cmrbkr asked MartinJaffer-MSFT edited

Adding datetime as dynamic content to a query

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.86304-screen-shot-2021-04-09-at-85531-am.png



This is the error I get
86322-screen-shot-2021-04-09-at-85812-am.png


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.

azure-data-factorysql-server-transact-sqlpower-query-not-supportedazure-databricks
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.

nasreen-akter avatar image
3 Votes"
nasreen-akter answered nasreen-akter edited

Hi @cmrbkr,

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! :)



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.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

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.

cmrbkr avatar image
0 Votes"
cmrbkr answered nasreen-akter commented

Thank you for both answering so quickly.
@Viorel-1 - 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.86371-screen-shot-2021-04-09-at-100715-am.png



· 1
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.

MartinJaffer-MSFT avatar image
1 Vote"
MartinJaffer-MSFT answered MartinJaffer-MSFT edited

@cmrbkr
@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')}'}



· 2
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.

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!

1 Vote 1 ·

Thank you for the feedback.

0 Votes 0 ·
cmrbkr avatar image
1 Vote"
cmrbkr answered MartinJaffer-MSFT commented

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!

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.