Solved

MySQL Time Date Fields

Posted on 2014-04-03
6
692 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 350 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 150 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

756 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