Comparing File dates and times in ADF

Hello @Pendleton, David

There are a number of possible issues :

1. Empty Braces in 'If condition' Activity:
   • This suggests that either the output of the Get Target File Metadata activity or the value stored in the PreviousModifiedDate variable might be empty or null.
   • The @greater function then likely fails to compare the dates correctly due to this missing data, leading to unpredictable results.
2. 'Random' File Selection:
   • Since the comparison within the If condition is likely not functioning as intended, the subsequent actions in your Logic App might execute randomly or based on some other unrelated criteria.
3. Date Formatting Mismatch:
   • While it's less likely, ensure that both dates are being formatted consistently using 'yyyy-MM-dd HH:mm:ss'. A mismatch could lead to unexpected comparison results.

Troubleshooting Steps:

Check for Empty Values:

• Add a Compose action before the If condition to inspect the output of Get Target File Metadata and the value of PreviousModifiedDate.
  • Ensure both contain valid date/time values and are not empty or null.
    • If either is empty, investigate why that activity/variable isn't populated correctly.
    Explicitly Handle Nulls:

    - Consider adding a `coalesce` function to handle the possibility of null values. For example:
    
    ```javascript
    @greater(coalesce(formatDateTime(activity('Get Target File Metadata').output.lastModified, 'yyyy-MM-dd HH:mm:ss'), '1900-01-01 00:00:00'), 
    

coalesce(formatDateTime(variables('PreviousModifiedDate'),'yyyy-MM-dd HH:mm:ss'), '1900-01-01 00:00:00')) ```

           - This will replace null dates with a default value, preventing errors in the comparison.
           
           **Verify Date Formatting:**
           
              - Double-check that both dates are consistently formatted using `'yyyy-MM-dd HH:mm:ss'`.
              
              **Enable Run History with Logging:**
              
                 - Turn on detailed logging in your Logic App's Run History to track the values of variables and activity outputs throughout the execution. This will help pinpoint where the issue originates.
                 

Example Fix (Assuming Empty lastModified):

If the lastModified property is sometimes empty, you might need to add a condition to handle that case specifically:

@if(empty(activity('Get Target File Metadata').output.lastModified), false, 
@greater(formatDateTime(activity('Get Target File Metadata').output.lastModified, 'yyyy-MM-dd HH:mm:ss'), 
formatDateTime(variables('PreviousModifiedDate'),'yyyy-MM-dd HH:mm:ss')))

Kindly follow the suggestions and let us know the results !

--

I hope this helps!

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

I verified that the 'Get Target Metadata Activity' is returning Item name and Last modified, for each file in the container (via a ForEach activity) just as I configured it. The output looks like this:

{
	"itemName": "customer_hierarchy_01_24_2024_19_53_03.csv",
	"lastModified": "2024-01-24T19:53:13Z",
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US 2)",
	"executionDuration": 1,
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	},
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	}
}

The 'Set variable' activity output looks like this:

{
	"name": "PreviousModifiedDate",
	"value": "2024-05-22T19:01:39Z"
}

The 'if comparison' is still empty; no error is generated, and the 'True' portion of the activity is executed every time.

Hello @Pendleton, David

Thank you for your feedback , it seems there is an issue with the If condition expression or the data types being compared. Even though the Get Target File Metadata activity is returning values for itemName and lastModified, and the Set variable activity is correctly setting PreviousModifiedDate, the If condition expression is likely not evaluating correctly due to a data handling or expression logic issue.

1. Ensure Correct Date Format and Null Handling

Instead of formatting dates to a string and comparing them, compare them directly as ISO8601 datetime strings.

Here is a refined expression to use in the If condition:

@if(
    or(
        empty(activity('Get Target File Metadata').output.lastModified), 
        empty(variables('PreviousModifiedDate'))
    ), 
    false, 
    greater(
        ticks(activity('Get Target File Metadata').output.lastModified),
        ticks(variables('PreviousModifiedDate'))
    )
)

2. Double-Check the Output of Previous Activities

You mentioned that the Get Target File Metadata returns the following output:

{

"itemName": "customer_hierarchy_01_24_2024_19_53_03.csv",

"lastModified": "2024-01-24T19:53:13Z",

...

And the Set variable activity output is:

}

{

"name": "PreviousModifiedDate",

"value": "2024-05-22T19:01:39Z"

}

Both seem correctly formatted as ISO8601 datetime strings. Therefore, there is no need for additional formatting using formatDateTime().

3. Inspect Run History for Insights

• Enable Logging: Ensure detailed logging is enabled for the Logic App or Data Factory pipeline. This will help you trace the exact values being passed to the If condition.
• Inspect Inputs and Outputs: Look at the run history to see if the inputs to the If condition are correctly populated and formatted as expected.

4. Test with Sample Values

To verify the logic, consider temporarily replacing dynamic values with static sample values to confirm the If condition behaves as expected:

@if(

greater(

ticks('2024-01-24T19:53:13Z'),

ticks('2024-05-22T19:01:39Z')

)

)

--

I hope this helps!

The answer or portions of it may have been assisted by AI Source: ChatGPT Subscription

Kindly mark the answer as Accepted and Upvote in case it helped!

Regards

Yes, it is definitely the @greater function.

If I change it to @greater(0, 1), it behaves as you would expect, however it still doesn't show anything in the Input or Output windows.

I will try your expression since using plain strings didn't work either, even when they were in the correct format.

Thank you for your very thorough help.