MySQL: Why is the date object always showing "0"?

Question

I am trying to create a table with one column in date format. Below is my code for doing so:

CREATE TABLE Sales
    (`CustomerID` int, `mydate` date,  `amount` float(2))
;

INSERT INTO Sales
    (customerID, mydate, amount)
VALUES
    (10, 8/2/96,  2540.78),
    (20, 1/30/99, 1800.54),
    (30,7/14/95, 460.33), 
    (10, 6/29/98,2400),
    (50, 2/3/98, 600.28),
    (60, 3/2/98, 720),
    (10, 7/6/98, 150)
;

But my outcome is always:

10|0|2540.78                                                                                                                              
20|0|1800.54                                                                                                                              
30|0|460.33                                                                                                                               
10|0|2400.0                                                                                                                               
50|0|600.28                                                                                                                               
60|0|720.0                                                                                                                                
10|0|150.0

So basically the date column is only showing 0.

I am using this online IDE to help me execute this code (it's an online IDE executing MySQL I believe): https://www.tutorialspoint.com/execute_sql_online.php

I tried change the data type to "timestamp" or "datetime" but none of them work. Any help is appreciated!

mysql default date format for insert is YYYY-MM-DD so as a string so 8/2/96 would be '1996-8-2' this allows MySQL to implicitly cast the string to a date; otherwise the database is trying to do math by taking 8/2/96 and convert that to a date which seems to be returning 0. — xQbert, Nov 15 '16 at 18:54

score 4 · Answer 1 · edited May 23 '17 at 10:32

4

Use the proper mySQL date format and it should work..

http://dev.mysql.com/doc/refman/5.7/en/datetime.html

"... The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'...."

INSERT INTO Sales
    (customerID, mydate, amount)
VALUES
    (10, '1996-8-2',  2540.78),
    (20, '1999-1-30', 1800.54),
    (30, '1995-7-14', 460.33), 
    (10, '1998-6-29', 2400),
    (50, '1998-2-3',  600.28),
    (60, '1998-3-2',   720),
    (10, '1998-7-6',   150)

On an additional note, don't store amounts (currency) as floats. Anything where you want a SPECIFIC number of decimals, and not have errors in floating point math, needs to be stored as NUMERIC or DECIMAL with scale and precision. So instead of amount float(2) use amount numeric(10,2) (set the 10 to the max value to left of decimals planned, and then add 5 is my rule. (3 because of growth 2 for the decimals (add more if you need more precision) Assuming 8 values before decimal allowed and 2 decimals.

Simply put: Using floating point or double datatypes anytime you want a retain specific values for decimals is asking for trouble later.

Think of using float and double datatypes when precision doesn't matter such as large numbers like 6.22*10^23 (avagadro's number) typically don't care about anything after 6.22 Very large numbers or very small numbers in scientific arena's make sense but seldom if ever when dealing with currency where precision matters.

More on it:

Best data type to store money values in MySQL
Difference between float and decimal data type
Difference between numeric, float and decimal in SQL Server (sql server specific but relevant)
Difference between decimal, float and double in .NET?

Working SQL Fiddle

edited May 23 '17 at 10:32

Community

1
1

answered Nov 15 '16 at 18:56

xQbert

34,733
2
41
62

Thank you so much for your answer, xQbert! But are the quotation marks around the dates required? – alwaysaskingquestions Nov 15 '16 at 18:57
2

Yes, otherwise it is a mathematical expression ;) – Jeremy Harris Nov 15 '16 at 18:58
Wow. good to know! I was definitely googling the wrong things. Thanks so much guys!!! :) – alwaysaskingquestions Nov 15 '16 at 19:01
And thanks so much for the edit! then when do we use floats then? – alwaysaskingquestions Nov 15 '16 at 19:03
Thank you so much @xQbert!! I thought float would be a number with decimal hence more accurate, or at least that's what I learned in Java. This is quite counterintuitive. – alwaysaskingquestions Nov 15 '16 at 19:15
Pretty sure the same problem (same rule applies to) Java... http://stackoverflow.com/questions/3730019/why-not-use-double-or-float-to-represent-currency – xQbert Nov 15 '16 at 19:17
Wow. so i've been having the misconception the entire time! Thanks so much for correcting my misunderstanding. This definitely helped! – alwaysaskingquestions Nov 15 '16 at 19:19
You're not the only one. (evidenced by the number of questions I've linked and the responses you see (look at the vote count on some of those!!) I don't think it's well represented in initial programming classes; and until you actually have to deal with money do you really understand the problem. – xQbert Nov 15 '16 at 19:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128201/discussion-between-alwaysaskingquestions-and-xqbert). – alwaysaskingquestions Nov 15 '16 at 19:25

MySQL: Why is the date object always showing "0"?

1 Answers1