Change date format in sql table

Question

Hi there,

We currently have a table column where the datatype is NVARCHAR(15), which is used to store a date in the format 16/04/2021. We would like to be able to change that format of the existing rows in the table to 20210416.

Is this possible?

Any guidance would be appreciated

Thank you

Answer 1

If all the date strings are valid d/m/y dates, the query below will do the job. You'll need to add TRY_CAST or TRY_CONVERT if you have existing bad date values to avoid a conversion error, and remediate the invalid values as desired.

SET DATEFORMAT dmy;
UPDATE dbo.YourTable
SET ThisColumnShouldBeDateDataType = CONVERT(char(8), CAST(YourColumnn AS date), 112);

As suggested by the column name in the example query, it is best to store data with the proper data type and control display formatting in the application. This will help performance, reduce storage requirements, and improve data integrity.

Answer 2

If you cannot yet switch to appropriate types, the check this statement:

update MyTable
set MyColumn = concat(right(MyColumn, 4), substring(MyColumn, 4, 2), left(MyColumn, 2))
where MyColumn like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'

Answer 3

Hi @Robert Jubb ，

Please refer to:

    CREATE TABLE #yourtable(str varchar(15))     
    INSERT INTO #yourtable VALUES('16/04/2021')     
          
    UPDATE #yourtable     
    SET str=FORMAT(CONVERT (date,str,103),'yyyyMMdd')     
         
    SELECT str FROM #yourtable     

Output:

    str     
    20210416     

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.