CODEWITHSUNDEEP
mysqlsqldatetime
Hamreen Ahmad
Hamreen Ahmad

Reputation: 568

Truncated incorrect date value: '02:02:02 am'

When i insert this time '02:02:02 am' to a mysql time field using the following Query:

insert INTO invoices(name,phone,totalsum,givenmoney,date,time,moreinfo) VALUES ('f','2','2','2','1994-01-01',DATE_FORMAT('02:02:02 am','%h:%i:%s %p'),'AA')

It shows the following error

Truncated incorrect date value: '02:02:02 am'

how to solve it?

Not that the datatype of my time field is TIMESTAMP

Upvotes: 0

Views: 601

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You are close but you should use str_to_time instead

mysql> select str_to_date('02:02:02 am','%h:%i:%s %p');
+------------------------------------------+
| str_to_date('02:02:02 am','%h:%i:%s %p') |
+------------------------------------------+
| 02:02:02                                 |
+------------------------------------------+
1 row in set (0.00 sec)

Instead of timestamp I would suggest to use time data type for storing the time value H:i:s format http://dev.mysql.com/doc/refman/5.7/en/time.html

UPDATE : Getting some error while running the query by SO Here is a test case ran on mysql 5.5

mysql> create table invoices (
    ->    name varchar(30),
    ->    phone varchar(30),
    ->    totalsum varchar(30),
    ->    givenmoney varchar(30),
    ->    date date,
    ->    time time,
    ->    moreinfo varchar(30)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> insert INTO invoices
    -> (name,phone,totalsum,givenmoney,date,time,moreinfo) 
    -> VALUES 
    -> ('f','2','2','2','1994-01-01',str_to_date('02:02:02 am','%h:%i:%s %p'),'AA');
Query OK, 1 row affected (0.04 sec)

mysql> select * from invoices ;
+------+-------+----------+------------+------------+----------+----------+
| name | phone | totalsum | givenmoney | date       | time     | moreinfo |
+------+-------+----------+------------+------------+----------+----------+
| f    | 2     | 2        | 2          | 1994-01-01 | 02:02:02 | AA       |
+------+-------+----------+------------+------------+----------+----------+
1 row in set (0.00 sec)

for 5.6 here is the fiddle http://sqlfiddle.com/#!9/79e0c9/1

Upvotes: 1

Related Questions