How to convert Varchar type String date to date type date in dd/mm/yyyy format for entire column in a table in MySQL ?

Manoj Sai Kusuma 1 Reputation point
2022-05-28T06:06:33.537+00:00

I need help with converting a varchar type column in a table which has date stored as a string in 'dd/mm/yyyy' format. I would like to convert the entire column into date column using update table function. I have come across answers but they are all pointing to update single row at a time but I want to update the whole column in the table.

Azure SQL Database
Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
711 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2022-05-28T08:35:59.867+00:00

    First a meta-discussion: you have tagged your post with sql-server-general, azure-sql-database and azure-database-mysql. Of these, only the last pertains to MySQL. The other two relates to Microsoft SQL Server, which is a different product, and what applies to SQL Server may not apply to MySQL.

    I don't work with MySQL, only SQL Server, so I can only give you an outline.

    In the best of worlds it may as simple as: 

    ALTER TABLE tbl ALTER COLUMN col date [NOT] NULL

    This assumes three things:

    1. The engine actually supports this conversion. (True for SQL Server.)
    2. The engine's default interpretation of the string actually is dd/mm/yyyy. (True for SQL Server if you issue SET DATEFORMAT command first, or if your language setting is for instance, British, French or German - but not us_english.)
    3. All values in the column adheres to the format, and there are no junk values like 30/02/2022, 05/27/2022 or Kilroy was here. (Not at all unlikely.)

    If the latter proves to be true, the best may be (I've left out things I don't know how to do in MySQL): 

    ALTER TABLE tbl ADD newcol date

UPDATE tbl 
SET    newcol = cast(col AS date)
WHERE <check for date being a legal date>

ALTER TABLE tbl DROP COLUMN col

-- Rename newcol to col
    0 comments
  2. Naomi 7,361 Reputation points
    2022-05-29T12:57:10.02+00:00

    Can you post your table structure and what exactly do you have? E.g. if you're using SQL Server, can you post result of this
    SELECT @@VERSION

    ?

    Assuming your table has a different date column other than your varchar column, you can try 

    DECLARE @t TABLE(id INT IDENTITY(1,1) PRIMARY KEY, vDt VARCHAR(100), dt DATE NULL)

INSERT INTO @t (vDt) VALUES ('28/02/2022'), ('30/3/2021'), ('1/1/2000')

SET DATEFORMAT DMY

UPDATE @t SET dt = TRY_CAST(vDt AS DATE)

SELECT * FROM @t