| Bug #1415 | How to use date_format in insert statment | ||
|---|---|---|---|
| Submitted: | 27 Sep 2003 5:26 | Modified: | 1 Oct 2003 5:21 |
| Reporter: | Gautam Mukherjee | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | OS: | Windows (WIN-2K) | |
| Assigned to: | CPU Architecture: | Any | |
[27 Sep 2003 7:05]
Paul DuBois
This is not a bug. Input dates must be specified
in year-month-day order. Your date of '10-12-79'
is interpreted with 10 as the year, 12 as the month,
and 79 as the day of the month. There can be no
such date. You can see that MySQL cannot interpret
this a valid date by converting it to days and back
to a date like this:
mysql> select from_days(to_days('10-12-79'));
+--------------------------------+
| from_days(to_days('10-12-79')) |
+--------------------------------+
| NULL |
+--------------------------------+
For more information on valid date formats, see
the MySQL Reference Manual:
http://www.mysql.com/doc/en/DATETIME.html
If you have further questions, please see:
http://www.mysql.com/doc/en/Asking_questions.html
[28 Sep 2003 21:52]
Gautam Mukherjee
Dear Paul But in your reply you not said anything about the value from 1-31 in the place of 79, if i replace the value of 79 with any value betwenn 1 and 31 then it works correctly, so please reply me y it is not possible in case of value above 31.
[29 Sep 2003 1:25]
Alexander Keremidarski
Gautam, Please note that Bugs Database is not intended to provide support of any kind. It is dedicated for Bug report with repeatable test cases. Your questions simply don't fit here. "How To Use.." can't be categorized as bug by any mean. For general questions like yours you can use public list mysql@lists.mysql.com where our experts might answer you for free or you can consider using MySQL AB Support services as described www.mysql.com/support.
[30 Sep 2003 2:05]
Alexander Keremidarski
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Sorry, but such bug report can't be accepted. There is no How-to-repeat, no test case, not even minumin description of the problem. "Unpredictable" is something we can't work on. It can mean everything including hardware problems. Meanwhile we have fixed couple of corruption bugs in 4.0.15
[30 Sep 2003 21:42]
Gautam Mukherjee
Hello Sir
I HAVE A TABLE NAME ABC WITH COLUMNS X AS CHAR AND Y AS DATE
I clearly mentioned in first report that the insert statement is not working with any value above 31 in place of 79. If i used any values from 1-31 it works fine.
example
========
1) INSERT INTO ABC (X,Y) VALUES ('bbbb',DATE_FORMAT('10-12-21','%d-%m-%y'));
---- Avbove statment is working and it is seting the value "2021-12-10"
---Below statment is not working and inserting the value "Null".
2) INSERT INTO ABC (X,Y) VALUES ('bbbb',DATE_FORMAT('10-12-79','%d-%m-%y'));
Hope you understand the problem, and u will reply me.
[1 Oct 2003 5:21]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php please read the http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1333 about the DATE_FORMAT function. It converts *date* to the *string*, not vice versa. '10-12-21' means the YEAR=10, MONTH=12, DAY=21. DATE_FORMAT formats it as '%d-%m-%y', thus producing '21-12-10', you insert it into DATETIME field, thus it becomes YEAR=21 MONTH=12 DAY=10, which makes you believe your original statement "worked". But it did not.
Description: I HAVE A TABLE NAME ABC WITH COLUMNS X AS CHAR AND Y AS DATE I WANT TO USE A DATE FORMAT IN INSERT STATMENT SO PLZ SUGGEST ME HOW I CAN USE THE DATE FORMAT TO INSERT THE GIVEN EXAMPLE. INSERT INTO ABC (X,Y) VALUES ('bbbb',DATE_FORMAT('10-12-79','%d-%m-%y')); WHEN I TRIED THIS EXAMPLE IT ENTERED A NULL VALUE IN IT. IT WAS WORKING FINE TILL THE VALUE IS 1 TO 31 (AT THE PLACE OF 79) BUT AFTER THAT IT IS INSERTING NULL. IF POSSIBLE PLEASE REPLY ME SOON, I WILL BE VERY THANKFULL TO IF U CAN SOLVE MY THIS PROBLEM. REGARDS GAUTAM. How to repeat: WHEN I TRIED THIS EXAMPLE IT ENTERED A NULL VALUE IN IT. IT WAS WORKING FINE TILL THE VALUE IS 1 TO 31 (AT THE PLACE OF 79) BUT AFTER THAT IT IS INSERTING NULL.