ADF Dynamic SQL as query in cosmosdb returning syntax error

Question

I am trying to get the previous days records from cosmosdb using the below query

I have defined a variable named start using the set varaible.

@concat('select * from c where ', formatDateTime('[c.createdAt]','yyyy-MM-dd'), '>=', '''', variables('start'), '''')

I get a syntax error.

Can anyone please help me with what is wrong with the query ?

Answer 1

Hi [Indira Priya Darshini Bandari],

Thanks for reaching out to Microsoft Q&A.

1. It seems the way you're trying to format and concatenate the date variable into the SQL query is incorrect. The formatDateTime function should be used to format the variable before concatenation.
2. When you are constructing sql queries, ensure that string literals are properly enclosed in single quotes. You need to escape single quotes correctly in adf expressions.

Refer query #1

In SQL, string literals must be enclosed in single quotes. To include a single quote in a string in ADF, you need to use two single quotes (' ') . Ensure that variable start is correctly set to a valid date string before using it in the query.

For ex, If your variable start is meant to represent the previous day's date...

Refer query #2

can you try this and let me know

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.