Question: I want to change the date format in mm/yyyy. My query like that. 

```
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.

```

I use DATE\_FORMAT like that 

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

```

but it does not work properly.

Answer: 
***From the comments and post***: 

> 
> *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* 
> 
> 
> 

**Change your query as below**: 

```
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

```

Even if some the date values miss `dd` part, MySQL silently replaces them with `00` when converted from `str_to_date`. 

**Example**: 

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

```

**Result**: 

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

```
*try this:*

```
DATE_FORMAT(NOW(),'%m-%d-%Y')

```

***<http://www.w3schools.com/sql/func_date_format.asp>***

*your code as like:*

```
SELECT DATE_FORMAT(NOW(), '%M %Y');

SELECT DATE_FORMAT(a.date, '%M/%Y') FROM t1 AS a;

```

***<http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format>***

***sqlfiddle: <http://sqlfiddle.com/#!2/6a68f/2>***