SQL 'Conversion failed when converting date and/or time from character string.'

Question

I am creating a pipeline from the SQL database to storage in the Azure data factory.

I am struggling to identify how to search data between the two dates with this format: yyyy-MM-ddTHH:mm:ssZ

I created a variable for the date called: date

Now upon searching query with below codes:

@concat('SELECT * FROM dbo.Task_History 
WHERE history_timestamp BETWEEN ''2015-01-01T01:00:00Z'' AND CONVERT(datetime2, ''' , variables('date') , ''', 127)')

It gives an error stating:

Conversion failed when converting date and/or time from character string.

Below is the target column where to search the target data

Answer 1

​You can use convertFromUtc() function to get current date time in Singapore Standard time zone. And then you can use string interpolation syntax for dynamically building your SQL query.

In below example, I am trying to build a SQL query which will query data from "dbo.Task_History" table between dates "2015-01-01T01:00:00Z" & current date time Singapore Standard time zone.

Set variable activity to set current date time value in Singapore Standard time zone.

Expression used: @convertFromUtc(utcnow(),'Singapore Standard Time') Generating SQL query using String Interpolation format:

SELECT * FROM dbo.Task_History WHERE history_timestamp BETWEEN '2015-01-01T01:00:00Z' AND '@{variables('singaPoreDateTime')}'

Between, could you pls help me understand why you are trying to convert again in SQL to some timezone ?