Why this query is returning empty rows?

Question

I have data in this format

CREATE TABLE IF NOT EXISTS `historical_data` (
  `symbol_name` varchar(70) DEFAULT NULL,
  `current_day` varchar(50) DEFAULT NULL,
  `open_val` varchar(20) DEFAULT NULL,
  `high_val` varchar(20) DEFAULT NULL,
  `low_val` varchar(20) DEFAULT NULL,
  `close_val` varchar(20) DEFAULT NULL,
  `last_val` varchar(20) DEFAULT NULL,
  `prevclose_val` varchar(20) DEFAULT NULL,
  UNIQUE KEY `symbol_name` (`symbol_name`,`current_day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `historical_data` (`symbol_name`, `current_day`, `open_val`, `high_val`, `low_val`, `close_val`, `last_val`, `prevclose_val`) VALUES
    ('IBWSL', '10-DEC-2015', '9.35', '9.8', '9', '9.45', '9.6', '9.5'),
    ('ICICIBANK', '10-DEC-2015', '260.85', '261.9', '256', '258.95', '258.65', '259.45'),
    ('ICIL', '10-DEC-2015', '981', '1004.2', '981', '989.7', '992', '988.45'),
    ('ICNX100', '10-DEC-2015', '86.8', '87.99', '86.8', '87', '87', '82.5'),
    ('ICRA', '10-DEC-2015', '4175', '4280', '4144', '4211.4', '4279.95', '4175.3'),
    ('ZYDUSWELL', '14-DEC-2015', '815.75', '815.75', '785.25', '810.7', '810.5', '803.1'),
    ('ZYLOG', '14-DEC-2015', '2.9', '2.95', '2.85', '2.95', '2.95', '2.19');
/*!40000 ALTER TABLE `historical_data` ENABLE KEYS */;

I have written the query this way

select * from (
    select 
      symbol_name , 
      (prevclose_val-close_val) as losers,
      'daily' as `type` 
    from
      historical_data
    where
      current_day >= STR_TO_DATE('14-DEC-2015', '%d-%MMM-%Y')
    order by losers asc limit 10 
) as sub_daily

This is my sqlfiddle

http://sqlfiddle.com/#!9/b4819

Answer 1

Use STR_TO_DATE() function both side because you had stored date as varchar column in database.

You can also use DATE as DATATYPE to remove this type of date conversion in all queries

Try this:

SELECT * 
FROM (SELECT symbol_name , (prevclose_val-close_val) AS losers,'daily' AS `type` 
      FROM historical_data 
      WHERE STR_TO_DATE(current_day, '%d-%M-%Y') >= STR_TO_DATE('14-DEC-2015', '%d-%M-%Y') 
      ORDER BY losers ASC 
      LIMIT 10 
     ) AS sub_daily;

Check the SQL FIDDLE DEMO

::OUTPUT::

| symbol_name |              losers |  type |
|-------------|---------------------|-------|
|   ZYDUSWELL |  -7.600000000000023 | daily |
|       ZYLOG | -0.7600000000000002 | daily |