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

don't you need `STR_TO_DATE()` on `current_day` as well? – guido Dec 15 '15 at 12:10 — guido, Dec 15 '15 at 12:10

Saharsh Shah · Accepted Answer · 2015-12-15T12:08:04.897

2

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 |

edited Dec 15 '15 at 12:08

answered Dec 15 '15 at 12:05

Saharsh Shah

27,121
8
41
77

2

Or better yet, use DATE as the column type. And the quote columns should probably be numeric. – Klas Lindbäck Dec 15 '15 at 12:07
@KlasLindbäck I also prefer DATE datatype is used for the date column. – Saharsh Shah Dec 15 '15 at 12:09
@SaharshShah , thanks a lot , one more query with this i can find top loosers of a day , similarly is it possible to find out top loosers for the week based on the current data . please tell me how can i find that . – Pawan Dec 15 '15 at 12:19

score 0 · Answer 2 · answered Dec 15 '15 at 12:10

Your problem is because you created the field current_day as VARCHAR and it should be a DATE.

Also on your query you convert only the value to filter into a date and not the field itself.

So you should fix your table changing that field into a DATE of do this on your query where filter:

In the MySQL docs there is no %MMM format either change it to %b which is abbreviated month or %M month name the format parameter follows the DATE_FORMAT function format, see it here: DATE_FORMAT Formats

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

If you convert the field into a DATE type you can do just:

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

Why this query is returning empty rows?

2 Answers2