0

I have a table called test in my MySQL database. Below is the how it is created.

CREATE TABLE `test` (
 `update_date` varchar(10) NOT NULL,
 `value` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Below is its data.

enter image description here

I run the below query as described in the selected answer of here

SELECT SUM(`value`) FROM `test`
WHERE STR_TO_DATE(`update_date`, '%d-%m-%Y') BETWEEN '01-10-2014' AND '31-10-2014'

This should return 1800, but instead it returned NULL. Why is this and how can I get the correct answer?

Very Important: I have to use String for Date because I must save date in dd-mm-yyyy format and mysql Date do not accept it.

Community
  • 1
  • 1
PeakGen
  • 21,894
  • 86
  • 261
  • 463
  • Mysql date does NOT store the date in any specific format. In fact the date is usually stored as an offset from a base date (a decimal value). Don't use strings to store dates, use date. How your date is displayed or compared is up to you but converting from string to date all the time WILL impact performance – Charleh Oct 05 '14 at 08:28
  • 1
    Looking at the docs it suggests that `DATE` is stored as 3 bytes. The docs are misleading to newbies though as they say the data is retrieved and displayed in YYYY-MM-DD. This doesn't mean it's stored in that format. Don't use strings, convert your dates in your queries. Think about it - it takes one function call to convert a date parameter, but it takes one function call PER ROW to convert data in your tables to go from string to date. Add to that the fact that indexes won't be utilised because of the scalar applied to the column and you are asking for poor performance – Charleh Oct 05 '14 at 08:47
  • Just to throw a bit more ammo at justifications for not spring dates as strings http://en.m.wikipedia.org/wiki/Sargable – Charleh Oct 05 '14 at 09:01
  • @Charleh:You mean the `Date` object can be saved in `dd-mm-yyyy` ? I went through lot of questions, posts and examples, all say it is not. – PeakGen Oct 05 '14 at 13:17
  • It's not saved in any format - it's saved as a series of bits (3 bytes which is 24 bits, e.g. `000010100011100101011010`) which likely represent an offset from a point in the past. You retrieve dates in MySQL in `YYYY-MM-DD` format but how you display them is up to your application layer, not the database layer. If you are using the database layer to format a date you are usually doing it wrong. If you are storing dates as strings you will impact performance because converting a date string is costly vs comparing the 24 bits a date is stored as. Format the date in your app not your SQL – Charleh Oct 05 '14 at 15:40

1 Answers1

0

Try this:

 SELECT SUM(`value`) FROM `test`
 WHERE STR_TO_DATE(`update_date`, '%d-%m-%Y') 
 BETWEEN STR_TO_DATE('01-10-2014', '%d-%m-%Y') AND STR_TO_DATE('31-10-2014', '%d-%m-%Y')

or you could also do this:

SELECT SUM(`value`) FROM `test`
WHERE STR_TO_DATE(`update_date`, '%d-%m-%Y') 
BETWEEN '2014-10-01' AND '2014-10-31'  -- in Y-m-d

Both the queries should return 1800.

Note that STR_TO_DATE() returns the DATETIME value ,which is in Y-m-d format.

phpcoderx
  • 570
  • 5
  • 16