Mysql:更改日期格式

Question

我想更改日期格式(mm/yyyy)。我的查询就是这样。

select shipwynum,
        IF (s.adjdeldat != '',  s.adjdeldat,s.condeldat) as -- adjdeldat and condeldat are date type
        deliverydate,
FROM ship
Result of that query come in yyyymmdd fromat but i want result in mm/yyyy format.

我像这样使用 DATE_FORMAT

DATE_FORMAT(s.adjdeldat,'%m/%Y')
DATE_FORMAT(s.condeldat,'%m/%Y')

但它无法正常工作。

Answer 1

来自评论和帖子:

Data type is varchar for adjdeldat and condeldat and it save data in yyyymmdd or yyyymm

I want to change the date format in mm/yyyy

You mean some data is in 20140415 format and some in 201404 format?

Yes, and i want that data in 04/2014 format

按如下方式更改您的查询:

select 
  shipwynum,
  if( s.adjdeldat != '',  
      date_format( str_to_date( s.adjdeldat, '%Y%m%d' ), '%m/%Y' ),
      date_format( str_to_date( s.condeldat, '%Y%m%d' ), '%m/%Y' ) 
  ) deliverydate,
from ship

即使某些日期值缺少 dd 部分，当从 str_to_date 转换时，MySQL 也会默默地用 00 替换它们。

示例:

select 
  @dt:=str_to_date( '201404', '%Y%m%d' ) dt, 
  date_format( @dt, '%m/%Y' ) df;

结果:

+------------+---------+
| dt         | df      |
+------------+---------+
| 2014-04-00 | 04/2014 |
+------------+---------+
1 row in set (0.00 sec)