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

JBH 1 Reputation point
2021-06-22T09:09:41.767+00:00

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
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 40,576 Reputation points
    2021-06-22T09:40:43.807+00:00

    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);  
    
    0 comments No comments