"Unable to parse the expression. Please make sure it is valid." when typing Dataflow Expression Builder

Question

I'm working with Azure Synapse Analytics and I'm facing an issue with the Data Flow expression builder. My goal is to format a column named "Start Date (BST)" which currently has the format yyyy-MM-ddTHH:mm:ss.SSSSSS to dd/MM/yyyy HH:mm:ss, effectively removing the milliseconds.

Here's what the date looks like right now:

2022-07-07T23:43:13.722007

I tried using the following expression in the data flow expression builder:

toTimestamp('Start Date (BST)', 'dd/MM/yyyy HH:mm:ss:SS')

However, I am getting an error stating "Unable to parse the expression. Please make sure it is valid."

Could someone please guide me on the correct syntax or the steps I should take to format the datetime values properly using the expression builder in Azure Synapse? I would appreciate any insights or examples of the correct expression to achieve this.

Thank you in advance!

Answer 1

You expression as below.

toString(toTimestamp({Start Date (BST)},'yyyy-MM-dd\'T\'HH:mm:ss'),'dd/MM/yyyy HH:mm:ss')

You need to give column name aroung curly braces and need to add T like 'T' but this will take it as quote so escape it with slash \ like this \'T\', then format it with using to string.

Output:

But this gives you as a string if you want in timestamp remove toString function

and you get output as below.