Errors adding a day to a date in ADF script

Question

Errors adding a day to a date in ADF script

Yi Han Wong 40

I'm having an issue where I'm trying to add a day to a start date parameter, this is the script I used in the Script activity within ADF:

@concat('SELECT DATEADD(dd, 1, CONVERT(DATETIME, ''',variables('v_POLLED_DT'), '''))')

However I'm getting the following error message:

Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Execution.ScriptActivityExecutor,''Type=Apache.Arrow.Adbc.C.CAdbcDriverImporter+ImportedAdbcException,Message=[Snowflake] 000904 (42000): SQL compilation error: error line 1 at position 30 invalid identifier 'DATETIME',Source=Apache.Arrow.Adbc,''

Am I missing something here, as I'm pretty sure this is a valid T-SQL statement?

Yi Han Wong 40 Reputation points

2025-05-16T09:25:34.36+00:00
Hi Ganesh, many thanks, you are right in saying this, I did eventually managed to use the CAST function and it worked.

@concat('SELECT DATEADD(dd, 1, CAST(''',variables('v_POLLED_DT'),''' AS DATETIME))')

For context, this is what I'm trying to do:

I'm trying to run a copy activity against a large table in Snowflake, where there's a SELECT statement and a WHERE clause pointing at a variable, v_POLLED_DT. The Script activity then increases this date by a day, which I was able to get to work thanks to your help, and a Set variable activity to update v_POLLED_DT to the new value. I then encapsulate this logic in an Until activity in the hope that it iterates until v_POLLED_DT is equal to EndDate, but for some reason it is not running the copy activity the second time (I suspect something is wrong with the Until activity logic). Here's my logic and a few screenshots of the outputs for reference, would be great if you can offer some suggestions...

Until activity logic:

Parameters:

Increment POLLED_DT output:

Update POLLED_DT output:

Accepted answer

2 additional answers

Your answer

Yi Han Wong 40 Reputation points

2025-05-16T09:25:34.36+00:00

Hi Ganesh, many thanks, you are right in saying this, I did eventually managed to use the CAST function and it worked.

@concat('SELECT DATEADD(dd, 1, CAST(''',variables('v_POLLED_DT'),''' AS DATETIME))')

For context, this is what I'm trying to do:

I'm trying to run a copy activity against a large table in Snowflake, where there's a SELECT statement and a WHERE clause pointing at a variable, v_POLLED_DT. The Script activity then increases this date by a day, which I was able to get to work thanks to your help, and a Set variable activity to update v_POLLED_DT to the new value. I then encapsulate this logic in an Until activity in the hope that it iterates until v_POLLED_DT is equal to EndDate, but for some reason it is not running the copy activity the second time (I suspect something is wrong with the Until activity logic). Here's my logic and a few screenshots of the outputs for reference, would be great if you can offer some suggestions...

Until activity logic:

Parameters:

Increment POLLED_DT output:

Update POLLED_DT output:

Answer 1

Nandan Hegde 36,066 MVP Volunteer Moderator

As the system is Snowflake, plz leverage the below syntaxes :

@concat('SELECT DATEADD(''day'', 1, TO_DATE(''', variables('v_POLLED_DT'), '''))')

or

SELECT DATEADD('day', 1, TO_DATE(@{variable}))

Answer 2

@Yi Han Wong

The error occurs because you're using T-SQL functions (CONVERT and DATETIME) which are not supported in Snowflake. Snowflake uses a different SQL dialect, and the function CONVERT(DATETIME, …) is invalid there.

To add a day to a date in Snowflake, you should use the DATEADD function with a valid date conversion function like TO_TIMESTAMP. Here's how you can rewrite your script for the Script activity in Azure Data Factory:


@concat('SELECT DATEADD(DAY, 1, TO_TIMESTAMP(\'', variables('v_POLLED_DT'), '\'))')

Reference:

Snowflake DATEADD function: https://docs.snowflake.com/en/sql-reference/functions/dateadd
Snowflake TO_TIMESTAMP function: https://docs.snowflake.com/en/sql-reference/functions/to_timestamp

These functions are the correct way to handle date arithmetic and conversion in Snowflake.

I hope this information helps.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Answer 3

Yi Han Wong 40

I managed to fix the issue, it turns out the Until activity works by looping continuously until the condition is met, rather than using variable less than or equal to EndDate, I just need to change it to variable greater than EndDate and it worked

Share via

Errors adding a day to a date in ADF script

2 additional answers

Your answer