R.C.Nougain,
Tuesday, June 04, 2002, 1:56:13 PM, you wrote:
RCN> create table dt (dt datetime);
RCN> --- OK
RCN> insert into dt values ( date_format('04/06/2002 13:48:38', '%d/%m/%Y
RCN> %H:%i:%s'));
RCN> --- Surprisingly it enters NULL
RCN> insert into dt values ( date_format('2002/04/06 13:48:38', '%Y/%d/%m
RCN> %H:%i:%s'));
RCN> --- Surprisingly (not really) it enters correct date
RCN> WHY is it so? I want the first insert to work properly. But it seems it is
RCN> not working. By simply moving the year part in first place makes the
RCN> statement to work properly. Why? Is it a MySQL bug. Please respond. I
RCN> already invested almost a day in finding it out.
Please, check the manual!
1. You are using a wrong format for the 1st argument.
"DATE_FORMAT(date,format)
Formats the date value according to the format string. "
So, the first argument must be in the date format: 'YYYY-MM-DD
HH:MM:SS' or 'YYYYMMDDHHMMSS' or 'YYYY-MM-DD' etc.
Look:
mysql> select date_format('04/06/2002 13:48:38', '%d/%m/%Y %H:%i:%s');
+---------------------------------------------------------+
| date_format('04/06/2002 13:48:38', '%d/%m/%Y %H:%i:%s') |
+---------------------------------------------------------+
| NULL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
2. You are using a wrong format for the datetime column.
See:
mysql> select date_format('2002/04/06 13:48:38', '%d/%m/%Y %H:%i:%s');
+---------------------------------------------------------+
| date_format('2002/04/06 13:48:38', '%d/%m/%Y %H:%i:%s') |
+---------------------------------------------------------+
| 06/04/2002 13:48:38 |
+---------------------------------------------------------+
1 row in set (0.01 sec)
The result of DATE_FORMAT() is not correct for datetime column and
MySQL will insert '0000-00-00 00:00:00'.
mysql> insert into dt values
-> (date_format('2002/04/06 13:48:38', '%d/%m/%Y %H:%i:%s'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from dt;
+---------------------+
| dt |
+---------------------+
| 2002-06-04 10:05:44 |
| 0000-00-00 00:00:00 |
+---------------------+
2 rows in set (0.04 sec)
RCN> Thanks
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
