?
Solved

MySQL Time Date Fields

Posted on 2014-04-03
6
Medium Priority
?
715 Views
Last Modified: 2014-04-07
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
Comment
Question by:shaunwingin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 39977334
Hi

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

HTH

Ivo Stoykov
0
 

Author Comment

by:shaunwingin
ID: 39978018
Kindly be more specific and address my queries.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 1400 total points
ID: 39978041
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 39982207
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This video teaches viewers about errors in exception handling.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question