Copying one column to another while applying a conversion text to date

Question

Hi everyone,

Post edited & changed:

How could I copy a column into another in a same table while changing the data type meaning that I want to convert a text to date format;
I tried several thing including convert function but it s always the error message :
unable to convert str to date.

Let s imagine a TABLE named DATE_TABLE
2 columns Date (type date) (empty) and Date_text (varchar) full of dd/mm/yyy text date.

What could be the script on azure sql to copy the date_text into the date column after converting it into date format?

Thanks for your help.

KR

Answer 1

2 columns Date (type date) (empty) and Date_text (varchar) full of dd/mm/yyy text date.

That's British date format and depending on the default language setting of you SQL login the engine expects more US format.
But you can pass the format information to CONVERT function for conversion, here British = 103, see CONVERT => Date and Time styles
Additional option is to use the TRY_CONVERT function, which don't raise error, it returns NULL for not-convertable values instead.
So try it with

UPDATE DATE_TABLE  
SET Date = CONVERT(date, Date_text, 103);  
  
UPDATE DATE_TABLE  
SET Date = TRY_CONVERT(date, Date_text, 103);