如何在DD.MM.YY格式的VARCHAR日期字段中获取最新日期？

You’ve nailed the root cause here—this is absolutely a problem with how two-digit years (YY) are parsed by your database. Most database systems use a fixed century window when interpreting YY values, which can lead to newer dates being incorrectly mapped to the 1900s (or older dates to the 2000s) and throwing off your MAX() calculation. Here’s how to fix it, depending on your database:

For Oracle Databases #

Oracle has a dedicated format mask for this exact scenario: RR. Unlike YY which locks into the current century, RR uses smart century inference based on the current year:

• If the current year's last two digits are 00-49:
  • Input YY values 00-49 → mapped to 20xx
  • Input YY values 50-99 → mapped to 19xx
• If the current year's last two digits are 50-99:
  • Input YY values 00-49 → mapped to 19xx
  • Input YY values 50-99 → mapped to 20xx

Just swap out YY for RR in your query:

SELECT MAX(TO_DATE(my_date, 'DD.MM.RR')) FROM my_table;

This will correctly parse your two-digit years to the right century and return the actual latest date.

For MySQL Databases #

MySQL doesn’t have an RR equivalent, but you can either leverage its built-in %y parsing rules or explicitly handle the century with string manipulation:

1. Use built-in %y parsing: By default, MySQL maps %y values 00-69 to 20xx and 70-99 to 19xx. If your dates fit this range, just use:

   SELECT MAX(STR_TO_DATE(my_date, '%d.%m.%y')) FROM my_table;
   

2. Explicit century handling: If your date range doesn’t match MySQL’s default, manually append the correct century:

   SELECT MAX(STR_TO_DATE(
       CONCAT(SUBSTRING(my_date, 1, 6),
              CASE WHEN SUBSTRING(my_date, 7, 2) <= 24 THEN '20' ELSE '19' END, -- Adjust 24 to match your current year's last two digits
              SUBSTRING(my_date, 7, 2)),
       '%d.%m.%Y'
   )) FROM my_table;
   

Universal Approach (Works for Most Databases) #

If you’re unsure of your database’s default parsing rules, the safest bet is to convert the two-digit year to a four-digit year explicitly before converting to a date. For example, if all your dates are from 1990 onwards:

SELECT MAX(TO_DATE(
    CONCAT(LEFT(my_date, 6),
           CASE WHEN CAST(RIGHT(my_date, 2) AS INTEGER) >= 90 THEN '19' ELSE '20' END,
           RIGHT(my_date, 2)),
    'DD.MM.YYYY'
)) FROM my_table;

Adjust the CASE logic to match your actual date range (e.g., if you have dates up to 2050, use <= 50 THEN '20' ELSE '19').

Why Your Original Query Failed #

When using YY, most databases lock the century to the current one (or a fixed hardcoded range, like 1900-1999 in some legacy systems). For example, if your database treats all YY values as 19xx, a date like 01.01.24 would be parsed as 1924—which is older than 1999, so MAX() returns 1999 instead of the actual 2024 date. Using RR or explicit century handling fixes this by mapping the two-digit year to the correct century.

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