mysql - 如何使用mysql以 "February, Seventeenth 2016"之类的字样显示日期

Question

我有日期 2016-02-17，我使用的是 DATE_FORMAT('2016-02-17', '%M, %D %Y') 和像这样显示'february, 17th 2016'，但我想显示february, 17th 2016，也就是word中的17th，请帮忙我

Answer 1

你可以尝试这样的事情:

SELECT
    CONCAT(
        MONTHNAME(NOW()),
        ',',
        SUBSTRING_INDEX(
            SUBSTRING_INDEX('First Second Third Fourth Fifth Sixth Seventh Eighth Ninth Tenth 11th 12th 13th 14th 15th 16th 17th 18th 19th 20th 21st 22nd 23rd 24th 25th 26th 27th 28th 29th 30th 31st', ' ' ,DAY(NOW())),
            ' ',
            - 1
        ),
        ' ',
        YEAR (NOW())
    );

注意:将 NOW() 替换为您的日期字段，并根据需要更新 SUBSTRING_INDEX('First Second....。

演示:

SET @sampleDate  := '2016-02-17';

SELECT
    CONCAT(
        MONTHNAME(@sampleDate),
        ', ',
        SUBSTRING_INDEX(
            SUBSTRING_INDEX('First Second Third Fourth Fifth Sixth Seventh Eighth Ninth Tenth 11th 12th 13th 14th 15th 16th 17th 18th 19th 20th 21st 22nd 23rd 24th 25th 26th 27th 28th 29th 30th 31st', ' ' ,DAY(@sampleDate)),
            ' ',
            - 1
        ),
        ' ',
        YEAR (@sampleDate)
    );

See Demo