mysql的excel格式日期

MySQL Excel格式日期的使用

sql
CREATE TABLE example (
    id INT,
    date_excel VARCHAR(10)
);

sql
SELECT id, DATE_FORMAT(STR_TO_DATE(date_excel, '%Y-%m-%d'), '%Y-%m-%d') AS date_standard
FROM example;

sql
SELECT id, DATEDIFF(STR_TO_DATE(date_excel_end, '%Y-%m-%d'), STR_TO_DATE(date_excel_start, '%Y-%m-%d')) AS days_diff
FROM example;

sql
-- 创建表
CREATE TABLE example (
    id INT,
    date_excel_start VARCHAR(10),
    date_excel_end VARCHAR(10)
);
-- 插入数据
INSERT INTO example (id, date_excel_start, date_excel_end)
VALUES (1, '43705', '43710');
-- 查询数据并转换日期格式
SELECT id, DATE_FORMAT(STR_TO_DATE(date_excel_start, '%Y-%m-%d'), '%Y-%m-%d') AS date_start_standard,
    DATE_FORMAT(STR_TO_DATE(date_excel_end, '%Y-%m-%d'), '%Y-%m-%d') AS date_end_standard
FROM example;
-- 计算日期差
SELECT id, DATEDIFF(STR_TO_DATE(date_excel_end, '%Y-%m-%d'), STR_TO_DATE(date_excel_start, '%Y-%m-%d')) AS days_diff
FROM example;