472,125 Members | 1,523 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,125 software developers and data experts.

Converting varchar field data into time

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.
Dec 19 '07 #1
5 7190
code green
1,726 Expert 1GB
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?
Dec 20 '07 #2
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.,
Dec 20 '07 #3
code green
1,726 Expert 1GB
I have never used it but you could try the MySql STR_TO_DATE().
This is from MySql manual
Expand|Select|Wrap|Line Numbers
  1. STR_TO_DATE(str,format) 
  3. This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. 
  4. 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. 
  6. The date, time, or datetime values contained in str should be given in the format indicated by format. 
  7. For the specifiers that can be used in format, see the DATE_FORMAT() function description. 
  8. If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL.
Dec 20 '07 #4
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
Dec 20 '07 #5
code green
1,726 Expert 1GB
I think it should be
Expand|Select|Wrap|Line Numbers
  1. SELECT STR_TO_DATE('04:02PM','%H:%i%p');
Dec 24 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Bill | last post: by
12 posts views Thread by Frederik Vanderhaeghe | last post: by
5 posts views Thread by Patti | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.