MySQL Time Date Fields

Say, I have a Time Value contained in a Field called "Time". The format is HH:MM AM/PM eg. 05:23 PM.
I wish to select all records that are > than a certain time value and are < a certain time value. I'm using VBA and the database is a MySQL. Kindly suggest the appropriate query in order to achieve this. Note the data is not yet in the Time Date field as it has not yet been imported and it is in  a text field. I can convert it to a time field but not sure how. It would require obviously copying to a new table which I can do. Alternatively, can just work with it as a Text Field.
shaunwinginAsked:
Who is Participating?
 
mankowitzCommented:
if you need to convert a string to time, use str_to_date. For example

select str_to_date("4:23 AM", "%h:%i %p")

would return the result in datetime format. From there, you can use the between operator like this

SELECT * FROM t WHERE
str_to_date(timefield, "%h:%i %p")  BETWEEN
str_to_date("3:00 AM", "%h:%i %p") AND
str_to_date("4:00 AM", "%h:%i %p")

which would show you all rows between 3 and 4 amd
0
 
Ivo StoykovCommented:
Hi

there is a between clause about this. Look here.
There are examples here.

HTH

Ivo Stoykov
0
 
shaunwinginAuthor Commented:
Kindly be more specific and address my queries.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hi,
 
 please clarify/confirm: the field "time" is currently text data type?
 if yes, you should indeed change that to a (different) field: TIME
 http://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
 http://dev.mysql.com/doc/refman/5.7/en/time.html

 note: instead of AM/PM you shall use 24 hour notation, will make things much easier

 once you have that, in MySQL, such a query is simple, mankowitz has posted the query structure above already: so what exactly are you missing?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.