时间戳

首页 > 解决方案 > 使用phpMyAdmin SQL查询的日期显示格式

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

问题描述

当我在 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;

标签： mysqlsqldatedatetime

解决方案

你要：

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

以下尝试很接近：

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

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

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

推荐阅读