SQL字符串日期转datetime方法及旧库日期字段转换查询方案

Hey there! Let's tackle your SQL date conversion problem step by step—this is a super common scenario with legacy databases, so I've got you covered. The core issue here is translating those varchar-formatted dates (like 24/04/2018 14:25:29 pm) into proper datetime types so you can use date functions like MONTH() and YEAR() for your queries.

一、Key Concepts First #

The main thing to remember is that you need to tell your database exactly what format your string dates are in—different SQL dialects use different functions for this. Also, while you can convert dates on the fly for one-off queries, it's way better to permanently change the column type if you'll be doing date-based queries regularly (it'll save you performance headaches later).

二、Database-Specific Solutions #

Let's break this down by the most common databases:

1. MySQL/MariaDB #

（1）On-the-Fly Conversion for Queries

Use the STR_TO_DATE() function, which takes your string date and a format pattern to parse it correctly:

-- Example conversion for your date format
SELECT STR_TO_DATE('24/04/2018 14:25:29 pm', '%d/%m/%Y %H:%i:%s %p') AS converted_date;

Here's what the format codes mean:

• %d: 2-digit day (01-31)
• %m: 2-digit month (01-12)
• %Y: 4-digit year
• %H: 24-hour format hour (00-23) — use %h instead if your dates use 12-hour time (e.g., 02:25:29 pm)
• %i: 2-digit minute
• %s: 2-digit second
• %p: AM/PM indicator

Your monthly count query would look like this:

SELECT COUNT(*) AS monthly_count
FROM your_table
WHERE MONTH(STR_TO_DATE(create_at, '%d/%m/%Y %H:%i:%s %p')) = MONTH(CURRENT_DATE())
  AND YEAR(STR_TO_DATE(create_at, '%d/%m/%Y %H:%i:%s %p')) = YEAR(CURRENT_DATE());

（2）Permanently Change Column Type (Recommended)

Do this once and forget about conversion in every query:

-- Step 1: Add a temporary datetime column
ALTER TABLE your_table ADD COLUMN create_at_temp DATETIME;

-- Step 2: Populate the temp column with converted dates
UPDATE your_table SET create_at_temp = STR_TO_DATE(create_at, '%d/%m/%Y %H:%i:%s %p');

-- Step 3: Verify all data is correct, then swap columns
ALTER TABLE your_table DROP COLUMN create_at;
ALTER TABLE your_table CHANGE COLUMN create_at_temp create_at DATETIME;

-- Repeat for updated_at
ALTER TABLE your_table ADD COLUMN updated_at_temp DATETIME;
UPDATE your_table SET updated_at_temp = STR_TO_DATE(updated_at, '%d/%m/%Y %H:%i:%s %p');
ALTER TABLE your_table DROP COLUMN updated_at;
ALTER TABLE your_table CHANGE COLUMN updated_at_temp updated_at DATETIME;

⚠️ Critical: Back up your table before making these changes—better safe than sorry!

2. SQL Server #

（1）On-the-Fly Conversion

Use CONVERT() (or TRY_CONVERT() to avoid errors from bad date strings) with a format code that matches your date structure:

-- Convert your example date (format code 103 = DD/MM/YYYY)
SELECT CONVERT(DATETIME, '24/04/2018 14:25:29 pm', 103) AS converted_date;

If your dates use 12-hour time (e.g., 02:25:29 pm), use format code 100 instead:

SELECT CONVERT(DATETIME, '24/04/2018 02:25:29 pm', 100) AS converted_date;

Your monthly query:

SELECT COUNT(*) AS monthly_count
FROM your_table
WHERE MONTH(CONVERT(DATETIME, create_at, 103)) = MONTH(GETDATE())
  AND YEAR(CONVERT(DATETIME, create_at, 103)) = YEAR(GETDATE());

（2）Permanently Change Column Type

SQL Server lets you alter the column directly if all dates are valid, but if you run into errors, use a temp column:

-- Option 1: Direct conversion (if no invalid dates)
ALTER TABLE your_table ALTER COLUMN create_at DATETIME;

-- Option 2: Use temp column if direct conversion fails
ALTER TABLE your_table ADD create_at_temp DATETIME;
UPDATE your_table SET create_at_temp = CONVERT(DATETIME, create_at, 103);
ALTER TABLE your_table DROP COLUMN create_at;
EXEC sp_rename 'your_table.create_at_temp', 'create_at', 'COLUMN';

-- Repeat for updated_at

3. PostgreSQL #

（1）On-the-Fly Conversion

Use TO_TIMESTAMP() with a format string to parse your date:

-- Convert your example date
SELECT TO_TIMESTAMP('24/04/2018 14:25:29 pm', 'DD/MM/YYYY HH24:MI:SS AM') AS converted_date;

Format code breakdown:

• DD: Day, MM: Month, YYYY: Year
• HH24: 24-hour hour, HH12: 12-hour hour
• MI: Minute, SS: Second, AM: AM/PM indicator

Your monthly count query:

SELECT COUNT(*) AS monthly_count
FROM your_table
WHERE EXTRACT(MONTH FROM TO_TIMESTAMP(create_at, 'DD/MM/YYYY HH24:MI:SS AM')) = EXTRACT(MONTH FROM CURRENT_DATE)
  AND EXTRACT(YEAR FROM TO_TIMESTAMP(create_at, 'DD/MM/YYYY HH24:MI:SS AM')) = EXTRACT(YEAR FROM CURRENT_DATE);

（2）Permanently Change Column Type

-- Step 1: Add temp column
ALTER TABLE your_table ADD COLUMN create_at_temp TIMESTAMP;

-- Step 2: Convert data
UPDATE your_table SET create_at_temp = TO_TIMESTAMP(create_at, 'DD/MM/YYYY HH24:MI:SS AM');

-- Step 3: Swap columns
ALTER TABLE your_table DROP COLUMN create_at;
ALTER TABLE your_table RENAME COLUMN create_at_temp TO create_at;

-- Repeat for updated_at

三、Pro Tips to Avoid Headaches #

• Validate Data First: Check for invalid dates (like 31/04/2018) before converting. Use functions like TRY_STR_TO_DATE() (MySQL), TRY_CONVERT() (SQL Server), or a CASE statement in PostgreSQL to catch bad entries.
• Performance Matters: Converting dates every time you query will force a full table scan—permanently changing the column type is worth the effort if you use date queries often.
• Backup Everything: I can't stress this enough—always back up your table before altering columns or updating large datasets.

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