mysql 3.23 datatime problem

[Sun]

Hi all,

I am inserting datatime value into mysql table, while select value its
giving 0000-00-00 00:00:00 value.


mysql> insert into dtest values('02-17-05 17:08:02');
Query OK, 1 row affected (0.00 sec)

mysql> select * from dtest;

+---------------------+
| mydate |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> desc dtest;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| mydate | datetime | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)


Any help to fix this problem.

-SR

[Dan Stumpus]

Re: mysql 3.23 datatime problem


"Sun" <sfgroups@gmail .com> wrote
[color=blue]
> mysql> insert into dtest values('02-17-05 17:08:02');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from dtest;
>
> +---------------------+
> | mydate |
> +---------------------+
> | 0000-00-00 00:00:00 |
> +---------------------+
> 1 row in set (0.00 sec)[/color]

This works:

insert into dtest values('2005-02-17 17:08:02');

Explained under the datetime datatype in the manual...

-- Dan

[Bill Turczyn]

Re: mysql 3.23 datatime problem

Although MySQL tries to interpret values in several formats, it always
expects the year part of date values to be leftmost. Dates must be
given in year-month-day order (for example, '98-09-04'), rather than in
the month-day-year or day-month-year orders commonly used elsewhere
(for example, '09-04-98', '04-09-98').

http://dev.mysql.com/doc/mysql/en/date-and-time-types.html

Hopes this helps.

[Bill Karwin]

Re: mysql 3.23 datatime problem

Sun wrote:[color=blue]
> mysql> insert into dtest values('02-17-05 17:08:02');[/color]

Try '2005-02-17 17:08:02'. MySQL accepts date formats only in one
format, YYYY-MM-DD.

Regards,
Bill K.