Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • Last Modified:

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.
0
shaunwingin
Asked:
shaunwingin
2 Solutions
 
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
 
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
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now