question

JeanBaptisteHerold-6014 avatar image
0 Votes"
JeanBaptisteHerold-6014 asked AnuragSharma-MSFT commented

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

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

azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JeanBaptisteHerold-6014, just wanted to check if you need any more details on thread. You can mark @OlafHelper-2800 as answer if that helped.

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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);


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.