在SQL Server中比较两个(mm/yyyy)格式的日期字符串

Hey David, I’ve run into this exact varchar date comparison headache before—string-based date checks are almost always a recipe for wrong results (like thinking '12/01/2024' is later than '01/15/2025' because "12" > "01"). Let’s break down how to fix this properly:

Step 1: Convert Varchar Dates to Actual Date Types #

The root issue is that you’re comparing strings instead of dates. Every major database has functions to convert formatted varchar strings into proper date values, which will compare correctly chronologically.

Example Queries by Database #

MySQL/MariaDB

Assuming your date strings are in MM/DD/YYYY format (adjust the format code if yours is different, like DD/MM/YYYY):

-- First, generate the lastPaymentCheck as a proper date
SELECT 
    customer_id,
    first_payment_date,
    DATE_ADD(STR_TO_DATE(first_payment_date, '%m/%d/%Y'), INTERVAL 6 MONTH) AS lastPaymentCheck,
    credit_card_expiry
FROM customers;

-- Then, identify users where their card expires before the payment plan ends
SELECT 
    customer_id,
    customer_name,
    credit_card_expiry,
    DATE_ADD(STR_TO_DATE(first_payment_date, '%m/%d/%Y'), INTERVAL 6 MONTH) AS lastPaymentCheck
FROM customers
WHERE STR_TO_DATE(credit_card_expiry, '%m/%d/%Y') < DATE_ADD(STR_TO_DATE(first_payment_date, '%m/%d/%Y'), INTERVAL 6 MONTH);

SQL Server

Using CONVERT with the correct style code (101 = MM/DD/YYYY):

-- Generate lastPaymentCheck
SELECT 
    customer_id,
    first_payment_date,
    DATEADD(MONTH, 6, CONVERT(DATE, first_payment_date, 101)) AS lastPaymentCheck,
    credit_card_expiry
FROM customers;

-- Identify at-risk users
SELECT 
    customer_id,
    customer_name,
    credit_card_expiry,
    DATEADD(MONTH, 6, CONVERT(DATE, first_payment_date, 101)) AS lastPaymentCheck
FROM customers
WHERE CONVERT(DATE, credit_card_expiry, 101) < DATEADD(MONTH, 6, CONVERT(DATE, first_payment_date, 101));

PostgreSQL

Using TO_DATE for conversion:

-- Generate lastPaymentCheck
SELECT 
    customer_id,
    first_payment_date,
    (TO_DATE(first_payment_date, 'MM/DD/YYYY') + INTERVAL '6 months')::DATE AS lastPaymentCheck,
    credit_card_expiry
FROM customers;

-- Identify at-risk users
SELECT 
    customer_id,
    customer_name,
    credit_card_expiry,
    (TO_DATE(first_payment_date, 'MM/DD/YYYY') + INTERVAL '6 months')::DATE AS lastPaymentCheck
FROM customers
WHERE TO_DATE(credit_card_expiry, 'MM/DD/YYYY') < (TO_DATE(first_payment_date, 'MM/DD/YYYY') + INTERVAL '6 months')::DATE;

Step 2: Handle Edge Cases #

• Invalid Date Strings: Some varchar entries might have typos (like '13/05/2024' for a month). Add a check to filter these out first, e.g., in MySQL:

  WHERE STR_TO_DATE(credit_card_expiry, '%m/%d/%Y') IS NOT NULL
  

• Different Date Formats: If your dates use DD/MM/YYYY instead, just swap the format codes (e.g., %d/%m/%Y in MySQL, 103 in SQL Server, 'DD/MM/YYYY' in PostgreSQL).

Long-Term Fix: Update Your Table Structure #

If possible, change the date columns from varchar to DATE or DATETIME types. This eliminates conversion overhead, prevents invalid date entries, and makes all date operations (comparisons, calculations) far more reliable. You can do this with an ALTER TABLE statement (backup your data first!):

-- Example for MySQL
ALTER TABLE customers MODIFY COLUMN first_payment_date DATE;
ALTER TABLE customers MODIFY COLUMN credit_card_expiry DATE;

Let me know if you need help tweaking the format codes or adjusting the payment interval to match your actual plan length!

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