469,579 Members | 1,899 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

Converting varchar field data into time

19
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 7029
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
ITCraze
19
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) 
  2.  
  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. 
  5.  
  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
ITCraze
19
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
By using this site, you agree to our Privacy Policy and Terms of Use.