title: MySQL, subquery return null Date and Datetime
body: <p>i have strange issue i have subquery that should return me information about holiday of user in given day</p>
  
  <pre><code>    SELECT `User`.`id`, `User`.`f_name`, `User`.`l_name`, `User`.`discharge`, `HourCard`.*, SUBSTRING(HourCard.date, 1, 10),
       (SELECT HOUR.date &lt;---- subquery
        FROM hours AS HOUR
        WHERE DATE(HOUR.date) = DATE(HourCard.date) AND Hour.subordinate_id = User.id AND Hour.day_off_id IS NOT NULL) AS info
  
      FROM `kadry`.`hour_cards` AS `HourCard`
      LEFT JOIN `kadry`.`users` AS `User` ON (`HourCard`.`subordinate_id` = `User`.`id`)
      WHERE `User`.`assigned` = 'HHDO1461932902' AND `User`.`active` = 1 AND DATE(`HourCard`.`date`) BETWEEN '2016-06-01' AND '2016-06-30'
      ORDER BY `User`.`l_name` ASC, `HourCard`.`date` ASC
  </code></pre>
  
  <p>query returns :</p>
  
  <pre><code>    | id | f_name | l_name | discharge | id | subordinate_id | date | date_type | readed | SUBSTRING(HourCard.date, 1, 10) | info | 
      | 00623 | john | doe | \null | 446610 | 00623 | 2016-06-01 17:06:12 | 1 | 0 | 2016-06-01 | \null | 
      | 00623 | john | doe | \null | 447779 | 00623 | 2016-06-02 08:00:49 | 0 | 0 | 2016-06-02 | \null | 
      | 00623 | john | doe | \null | 447778 | 00623 | 2016-06-02 17:13:53 | 1 | 0 | 2016-06-02 | \null | 
      | 00623 | john | doe | \null | 454696 | 00623 | 2016-06-03 08:03:40 | 0 | 0 | 2016-06-03 | \null | 
      | 00623 | john | doe | \null | 454697 | 00623 | 2016-06-03 17:03:36 | 1 | 0 | 2016-06-03 | \null | 
      | 00623 | john | doe | \null | 454699 | 00623 | 2016-06-04 08:05:07 | 0 | 0 | 2016-06-04 | \null | 
  </code></pre>
  
  <p>but when i change subquery to this </p>
  
  <pre><code>(
      SELECT HOUR.date
      FROM hours AS HOUR
      WHERE  Hour.date = '2016-06-01' AND Hour.subordinate_id = User.id AND Hour.day_off_id IS NOT NULL) AS info
  </code></pre>
  
  <p>this query return good result like this:</p>
  
  <pre><code>    | id | f_name | l_name | discharge | id | subordinate_id | date | date_type | readed | SUBSTRING(HourCard.date, 1, 10) | info | 
      | 02979 | johny | notDoe | \null | 455888 | 02979 | 2016-06-06 06:49:32 | 0 | 0 | 2016-06-06 | 2016-06-01 | 
      | 02979 | johny | notDoe | \null | 456766 | 02979 | 2016-06-06 19:00:32 | 1 | 0 | 2016-06-06 | 2016-06-01 | 
      | 02979 | johny | notDoe | \null | 457898 | 02979 | 2016-06-07 18:48:30 | 0 | 0 | 2016-06-07 | 2016-06-01 | 
      | 02979 | johny | notDoe | \null | 458947 | 02979 | 2016-06-08 06:59:45 | 1 | 0 | 2016-06-08 | 2016-06-01 | 
  </code></pre>
  
  <p>i tried using functions :</p>
  
  <pre><code>DATE_FORMAT(HourCard.date, "%Y-%m-%d") = DATE_FORMAT(HOUR.date,"%Y-%m-%d")
  DATE(HourCard.date) = DATE(HOUR.date)
  SUBSTRING(HourCard.date, 1, 10) = SUBSTRING(Hour.date, 1, 10)
  </code></pre>
  
  <p>but only inserting string as shown above produce expected results
  and coding in both tables are the same.
  Hour.date is <code>date</code> type and HourCard.date is <code>datetime</code>.</p>