Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Time Date Fields

Posted on 2014-04-03
6
Medium Priority
?
730 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

596 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