mysql - 使用phpMyAdmin SQL查询的日期显示格式

Question

当我在 phpMyAdmin 中运行 SQL 时，我的日期时间字段 (pm_expense_ledger.hos_date) 以这种格式显示:2019-10-01 00:00:00

虽然这不是最终用户 GUI，但如何将日期时间字段格式化为仅显示为 DDMMMYYYY(“%d %b %Y”)，因为运行查询时不需要齿？

你可以看到我已经尝试了几次但没有成功!

代码:

    SELECT pm_expense_ledger_el_connector.property_id AS PropId,
    /*    show FIELDS from pm_expense_ledger; to view field column types */
        pm_expense_ledger.hos_date AS Date, /* this is a datetime field */
    /*    DATE_FORMAT('pm_expense_ledger.hos_date', '%d%b%Y') AS FDate,    returns null values */
    /*    DATE_FORMAT("pm_expense_ledger.hos_date", "%d %b %Y %H-%i-%S") AS D1,*/
    /*    DATE('pm_expense_ledger.hos_date', '%d %b %Y') AS D2,*/
    /*    DATE("pm_expense_ledger.hos_date", "%d %b %Y") AS D3, no difference using single or double quotes */
    /*    DATA_TYPE('pm_expense_ledger.hos_date'),*/
        pm_homeowner_statement_key.key_name AS Item,
        pm_expense_ledger_description.descr AS Description,
        pm_expense_ledger_el_connector.hos_total AS HOS,
        pm_expense_ledger.pm_expense_ledger_id AS pmelid,
        pm_expense_ledger.expense_ledger_id AS elid
    FROM  pm_expense_ledger_el_connector
    JOIN pm_expense_ledger
        on pm_expense_ledger.pm_expense_ledger_id = pm_expense_ledger_el_connector.pm_exp_ledger_id
    JOIN pm_expense_ledger_description
        on pm_expense_ledger_description.pm_el_description_id = pm_expense_ledger.pm_exp_ledg_descr_id
    JOIN pm_homeowner_statement_key
        on pm_homeowner_statement_key.pm_hos_key_id = pm_expense_ledger.pm_hos_key_id

    where pm_expense_ledger.hos_date BETWEEN '2019,12,01' and '2019,12,31'
    /*group by pm_expense_ledger_el_connector.property_id*/
    order by pm_expense_ledger_el_connector.property_id ASC, pm_expense_ledger.hos_date ASC, pm_expense_ledger_id ASC;

Answer 1

你想要:

DATE_FORMAT(pm_expense_ledger.hos_date, '%d%b%Y') AS FDate

以下尝试已结束:

DATE_FORMAT('pm_expense_ledger.hos_date', '%d%b%Y')

它失败是因为列名周围有单引号，这使它成为一个字符串；由于此字符串无法转换为 datetime，因此 date_format() 会生成 null 值。

底线:在 MySQL 中，不要在标识符周围使用引号(单引号或双引号)；如果由于某种原因您需要引用标识符(因为它包含特殊字符)，那么您需要使用反引号(`)。