在mysql中的时间的数据类型,MySQL中的日期时间数据类型出错

I have created a table using below mensioned query in MySQL.

CREATE TABLE Pat_Visit (PatientID INT(16), FOREIGN KEY(PatientID) REFERENCES patient_demo(PatientID),Visit_DateTime DATETIME,Visit_Title VARCHAR(6),

DoctorID INT(16),FOREIGN KEY(DoctorID) REFERENCES Doctor(DoctorID));

and i have tried to add some data using query given below.

INSERT INTO Pat_Visit(PatientID,Visit_DateTime,Visit_Title,DoctorID) VALUES (100001,10/24/11 10:00 AM,'PAIN IN JOINTS',920001);

but it ios throwing error. I dont know what is the problem here.can anybody help me please...

解决方案

Try this instruction:

INSERT INTO Pat_Visit(PatientID,Visit_DateTime,Visit_Title,DoctorID)

VALUES (100001,'2011-10-24 10:00:00','PAIN IN JOINTS',920001);

You could convert your date like this:

STR_TO_DATE('10/24/11 10:00 PM','%m/%d/%Y %h:%i %p')

So the instruction is:

INSERT INTO Pat_Visit(PatientID,Visit_DateTime,Visit_Title,DoctorID)

VALUES (100001,STR_TO_DATE('10/24/11 10:00 PM','%m/%d/%Y %h:%i %p'),'PAIN IN JOINTS',920001);

MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS'

format. The supported range is '1000-01-01 00:00:00' to '9999-12-31

23:59:59'.