Query Ninja: Between!

User-added image

Be the Query Ninja: Between!
User-added imageThe SQL Select capabilities in MapInfo Pro include a very handy operator for selecting records between two values. If you do this sort of thing, you may find it more convenient than lumping together "greater than" and "less than" expressions. 

Note that the 64 bit version of MapInfo Pro was used for this article but the Between capability operates the same way in the 32 bit versions.

Example one: Selecting numeric values

We'll start with a table of customers for which we have the age of the customer. The column is (conveniently) called Age. For our purposes we want to select all of the customers who are aged between 35 and 55 years old. 

User-added image

By way of review, a way to do this is to use an expression such as:
Age >= 35 And Age <= 55

Here is an example of how to do the same thing using the Between operator: 

User-added image 

Note that the Between operator is inclusive of the lower and higher values. That means it includes both 35 and 55. The result is the same as this expression:
Age >= 35 And Age <= 55

Using Between with DateTime values

The "Between" operator works as expected for DateTime values. For example:

TimeValue Between "2:00 AM" And "10:00 PM" is true if the TimeValue is greater than or equal to "2:00 AM" and less than or equal to "10:00 PM"

In the above expression note that "AM" and "PM" are supported. If your data is formatted as 24 hour time you can omit "AM" and "PM".
TimeValue Between "02:00" And "22:00"

Wrapping around midnight

For time data, it is possible to query time values that "wrap-around" midnight. For example, let's say you have a database of crime incidents and you wish to query all of the crimes that happened between 10:00 pm and 2:00 am.

CrimeTime Between "10:00 PM" And "2:00 AM" is true if the CrimeTime is greater than or equal to "10:00 PM" and less than or equal to "11:59:59.999" or greater than or equal to "12:00 AM" and less than or equal to "2:00 AM" 

And, as mentioned above, it is possible to omit AM and PM in favour of 24 hour time. See the example below. 

User-added image

The focus here has been introducing you to the Between operator. This article has also touched on using time and date data. We'll go into more detail on working with the Date, Time and DateTime data types in a future article. 

Are you a Query Ninja yet? Want to learn more?

We have a great many articles on using the MapInfo Pro SQL capabilities in the LI360 Community. You can find a list of them all in the Topic Catalog. Look for the Query Ninja topic. 

Article by Tom Probert, Editor of "The MapInfo Pro" journal
When not writing articles for "The MapInfo Pro" journal, Tom enjoys talking to MapInfo Pro users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.
UPDATED:  June 28, 2017