Converting varchar field data into time

[ITCraze]

Please Guys help me out , as I need the solution of this problem very fast.Please help me.


My table contains a varchar field which contains time like : 04:09PM

Now the problem is that I have to convert this into time.Please help me to suggest how to convert this data into time as I have to create various reports.

I have also tried to put time datatype instead of varchar.But when I inserted this value -it gives error like this:


Incorrect time value: '04:09PM' for column 'time1' at row 1


Please suggest me what should I do and How should I perform this task.


Thanking you all.

[code green]

A MySql DATETIME field expects the format 'yyyy-mm-dd hh:mm:ss'.
So the times and dates inserted must follow that format.

My table contains a varchar field which contains time like : 04:09PM

A VARCHAR field contains strings not time.

Now the problem is that I have to convert this into time

Do you mean convert a VARCHAR field to a DATETIME field or convert your strings in to DATETIME values?

[ITCraze]

I mean to say that I am importing the data from txt file to MySql table.And that table contains time datatype:

and my txt file contains 04:02PM.So I have to move this data into that time field.And When I import this file to my table.I get the error:

Invalid data '4:02PM' for time .


So please help me out.,

[code green]

I have never used it but you could try the MySql STR_TO_DATE().
This is from MySql manual

STR_TO_DATE(str,format) 
 
This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. 
STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. 
 
The date, time, or datetime values contained in str should be given in the format indicated by format. 
For the specifiers that can be used in format, see the DATE_FORMAT() function description. 
If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL.

[ITCraze]

Thank you for replying so quickly to my answer.


I have tested the solution that you gave me but its giving me error:

SELECT STR_DATE('04:02PM','%r');

then its giving me error like :

Incorrect datetime value: '04:02PM' for function str_to_time

[code green]

I think it should be

SELECT STR_TO_DATE('04:02PM','%H:%i%p');