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

enter image description here

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.

enter image description here

Below is the target column where to search the target data

enter image description here

When you're constructing a query via string concatenation (but please lean towards using parameters instead to keep data and code separate and avoid SQL injection) and the query isn't working, please print out the entire final query. You'll either spot the issue yourself or should include it in your question. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 20, 2021 at 7:02
In any case dates in a SQL database have no format. They're binary values, just like int, bigint, bit, decimal etc. Client tools like SSMS display them using the ISO861 format because it's the only unambiguous format, not because the dates themselves have any format — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2021 at 7:29
@JuanEnriqueBanal it's not the table. It's the expression. You're wide open to SQL injection and conversion problems. Don't use concat. Use what the documentation shows - string interpolation with strongly typed parameters — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 20, 2021 at 8:47

IpsitaDash-MT · Accepted Answer · 2021-07-22 12:07:20Z

0

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 ?

answered Jul 22, 2021 at 12:07

IpsitaDash-MT

1,4801 gold badge5 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related