question

ManojSaiKusuma-9613 avatar image
0 Votes"
ManojSaiKusuma-9613 asked Cathyji-msft edited

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

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-databaseazure-database-mysql
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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
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.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN commented

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
· 2
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.

Yeah, that is good for SQL Server - but the question is about MySQL.

0 Votes 0 ·
NaomiNNN avatar image NaomiNNN ErlandSommarskog ·

You're right, I didn't read the title in full. Now I see it's MySQL question. I tried to do a quick search for TRY_CAST analogue in MySQL, looks like it's much harder in MySQL vs. SQL Server. https://stackoverflow.com/questions/27332240/how-to-know-cast-failed-in-mysql

0 Votes 0 ·