MySQL中如何删除早于2020年11月的字符串格式日期数据？如何将Varchar类型日期列无损转换为Date类型？

Hey there! Let's break down your two SQL challenges step by step—first fixing that flawed delete query, then safely converting your varchar date column to a proper datetime type without losing data.

1. Deleting Rows with Dates Earlier Than November 2020 #

First, let's clear up why your initial delete query didn't work:

DELETE FROM visited WHERE LEFT(last_visit_date, 2) != '01' OR LEFT(last_visit_date, 2) != '12';

This logic is broken because any value will satisfy the OR condition. For example, a date with month '02' isn't '01', so it gets deleted—even if it's from 2023. That's way broader than your goal of removing pre-November 2020 data.

Correct Approach: Convert the Varchar to a Date First #

Since your date string follows the MM/DD/YYYY HH:MI:SS AM/PM format, use MySQL's STR_TO_DATE() function to turn it into a proper datetime value, then compare it to your cutoff date (2020-11-01).

Step 1: Verify the Conversion Works

First, test that the conversion returns accurate dates:

SELECT 
  `last_visit_date`,
  STR_TO_DATE(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p') AS converted_date
FROM `visited`
LIMIT 10;

Make sure converted_date matches the original string's date/time.

Step 2: Check How Many Rows Will Be Deleted

Always verify the count before running a delete to avoid accidental data loss:

SELECT COUNT(*)
FROM `visited`
WHERE STR_TO_DATE(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p') < '2020-11-01';

Step 3: Run the Delete Query

Once you're confident in the count, execute the delete:

DELETE FROM `visited`
WHERE STR_TO_DATE(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p') < '2020-11-01';

2. Converting Varchar Date Column to Datetime Type (No Data Loss, Retain Display Format) #

Important note: Date/datetime types don't store display format—that's handled by how you query the data or your database client settings. But we can safely convert the column while preserving all date/time values, then set up queries to return the original format when needed.

Safe Conversion Steps (Backup First!) #

Always back up your table before making schema changes—better safe than sorry:

CREATE TABLE `visited_backup` AS SELECT * FROM `visited`;

Step 1: Add a Temporary Datetime Column

ALTER TABLE `visited` ADD COLUMN `last_visit_date_temp` DATETIME;

Step 2: Populate the Temporary Column with Converted Data

UPDATE `visited`
SET `last_visit_date_temp` = STR_TO_DATE(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p');

Step 3: Validate the Data

Double-check that no data was lost or corrupted:

SELECT 
  `last_visit_date`,
  `last_visit_date_temp`,
  DATE_FORMAT(`last_visit_date_temp`, '%m/%d/%Y %h:%i:%s %p') AS formatted_temp
FROM `visited`
LIMIT 20;

Confirm formatted_temp matches the original last_visit_date string exactly.

Step 4: Replace the Original Column

If validation passes, drop the old varchar column and rename the temporary column:

ALTER TABLE `visited` DROP COLUMN `last_visit_date`;
ALTER TABLE `visited` CHANGE COLUMN `last_visit_date_temp` `last_visit_date` DATETIME;

Step 5: Retain Original Display Format

To get the original MM/DD/YYYY HH:MI:SS AM/PM format when querying, use DATE_FORMAT():

SELECT DATE_FORMAT(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p') AS `last_visit_date`
FROM `visited`;

If you want this to be the default, create a view that applies this formatting automatically so your application doesn't need to change its queries:

CREATE VIEW `visited_formatted` AS
SELECT 
  *,
  DATE_FORMAT(`last_visit_date`, '%m/%d/%Y %h:%i:%s %p') AS `last_visit_date`
FROM `visited`;

内容的提问来源于stack exchange，提问作者GGG