如何通过MySQL获取数据库存储的dates？解决数据表日期显示异常

Alright, let's work through this MySQL date display issue together. Based on what you shared, here's how we can fix that annoying 01.01... placeholder showing up instead of your actual import dates:

• First, verify your table structures and field types
  Start by checking if your import table actually has a properly defined importdate field, and that it's using a date-compatible type like DATE or DATETIME (not VARCHAR, which can cause formatting chaos). Run these commands to inspect your tables:

  DESCRIBE import;
  DESCRIBE import_vorfälle;
  

  Look for the importdate field in the import table—check its type, whether it allows NULL values, and if it has a default value set to something like 0000-00-00 (which many clients render as 01.01...).

• Ensure your query correctly joins the two tables
  Since importdate lives in the import table, you need to join it with import_vorfälle using the import_id foreign key. If your current query isn't doing this, you'll never pull in the actual date values. Try this corrected query:

  SELECT 
    iv.*, 
    i.importdate 
  FROM import_vorfälle iv
  JOIN import i ON iv.import_id = i.id
  WHERE iv.vorfall IN ('变更', '错误'); -- Adjust the vorfall values to match your actual data
  

  Also, check for orphaned records in import_vorfälle—entries where import_id doesn't match any id in the import table. These will return NULL for importdate, which often gets displayed as 01.01...:

  SELECT * 
  FROM import_vorfälle iv
  WHERE NOT EXISTS (
    SELECT 1 FROM import i WHERE i.id = iv.import_id
  );
  

• Validate that importdate has actual data stored
  Sometimes the issue is that no dates are being written to the import table in the first place. Run this query to check the raw importdate values:

  SELECT id, importdate 
  FROM import 
  ORDER BY id DESC LIMIT 10;
  

  If you see NULLs or 0000-00-00 here, your import process isn't correctly populating the importdate field. Make sure your insertion logic uses something like CURDATE() (for current date) or passes a valid date string when creating new import records.

• Handle NULL/invalid dates in your output
  If some records legitimately don't have an import date, you can clean up the display to avoid the 01.01... placeholder. Use IFNULL or CASE to replace missing/invalid dates with a user-friendly message:

  SELECT 
    iv.*, 
    IFNULL(i.importdate, 'No import date recorded') AS importdate
  FROM import_vorfälle iv
  JOIN import i ON iv.import_id = i.id
  WHERE iv.vorfall IN ('变更', '错误');
  

  For 0000-00-00 values specifically:

  SELECT 
    iv.*, 
    CASE 
      WHEN i.importdate = '0000-00-00' THEN 'Invalid import date' 
      ELSE i.importdate 
    END AS importdate
  FROM import_vorfälle iv
  JOIN import i ON iv.import_id = i.id
  WHERE iv.vorfall IN ('变更', '错误');
  

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