SQL Between Clause with Strings Columns

SQL Between clause with strings columns

The expression

name between 'A' and 'B'

is equivalent to

name>='A' and name<='B'

So 'Argentina' is >='A' and <='B' and it satisfies the condition. But 'Bolivia' is NOT <='B'. 'Bolivia'>'B'. It doesn't just look at the first letter: it looks at the whole string. Which is surely the way it ought to be: if it didn't do this, there'd be no way to say that you wanted a range that included 'Smith' but not 'Smithers'.

To accomplish what you want, you could say:

substr(name,1,1) between 'A' and 'B'

or:

name like 'A%' or name like 'B%'

or:

name>='A' and name<'C'

SQL BETWEEN for text vs numeric values

Between is operating exactly the same way for numbers and for character strings. The two endpoints are included. This is part of the ANSI standard, so it is how all SQL dialects work.

The expression:

where num between 33 and 135

will match when num is 135. It will not match when number is 135.00001.

Similarly, the expression:

where food_name BETWEEN 'G' AND 'O'

will match 'O', but not any other string beginning with 'O'.

Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:

where food_name between 'G' and 'O~'

You can also do various other things. Here are two ideas:

where left(food_name, 1) between 'G' and 'O'
where food_name >= 'G' and food_name < 'P'

The important point, though, is that between works the same way regardless of data type.

Sql Between Clause for multiple columns

Use OR instead of AND opeartor

SELECT RECORD_TIMESTAMP AS DateRecorded, 
       Round (S_E2, 2)  AS S_E2, 
       Round (S_E1, 2)  AS S_E1 
FROM   TBL_SENSORS 
WHERE  RECORD_TIMESTAMP BETWEEN '4/28/2012 12 :00 AM' AND '5/17/2012 12 :00 AM' 
       or ( S_E2 BETWEEN 10 AND 100 ) 
       or ( S_E1 BETWEEN 10 AND 100 ) 

In the query, it is not guaranteed that you will get 111 records because there may be common records for two conditions.

condition 1 - ( S_E2 BETWEEN 10 AND 100 )

condition 2 - ( S_E1 BETWEEN 10 AND 100 )

Or

Use Union All in two queries to get all records (111).

select RECORD_TIMESTAMP as DateRecorded, ROUND (S_E1 ,2 )as S_E1, 0 as S_E2
from TBL_SENSORS
Where RECORD_TIMESTAMP Between '4/28/2012 12 :00 AM' and '5/17/2012 12 :00 AM'
And ( S_E1 Between 10 And 100 )

UNION ALL

select RECORD_TIMESTAMP as DateRecorded, 0 as S_E1, ROUND (S_E2 ,2 ) as S_E2
from TBL_SENSORS
Where RECORD_TIMESTAMP Between '4/28/2012 12 :00 AM'
and '5/17/2012 12 :00 AM'
And ( S_E2 Between 10 And 100 )

Between clause in sql server

This is because you're misunderstanding how dates work. This is further evidenced by the fact that you're passing and using your dates as strings

First off, dates internally to sql server are expressed as numbers of days since a certain date (midnight on 1/1/1900)

This means a date of 2 jan 1900 is internally expressed by sqlserver as 1.0. A date of 1800 hours on 2 jan 1900 is expressed internally as 1.75 - because there have been 1.75 days since midnight on the first. 6pm is three quarters the way through a 24 hour day hence the .75 part in the internal representation

With me so far?

it is the decimal point part that is defeating your logic

Let's see an example

Lets say you want records between 1 jan 1900 and 10 jan 1900, so really, you want records that have a time anything up to 23:59:59 (and 999999... milliseconds) on 10 jan 1900

This means you want records that are between 0.0 and 9.999999999 days after midnight on 1 jan....

But when you're running your query, you're just asking for:

BETWEEN #1 jan 1900# and #10 jan 1900#

In SQL terms this is

BETWEEN 0.0 and 9.0

And not what you want:

BETWEEN 0.0 and 9.9999999999999999999999999....

Ultimately, midnight is the very first thing that happens on a day. You won't get any records for 6am on the 10th jan because that is WELL AFTER midnight, it is a decimal number like 9.25

I'm sure you can appreciate that 9.25 is NOT BETWEEN 0.0 and 9.0

You will however get records that occurred exactly bang on midnight on the 10th, because they would internally be represented as 9.0, and 9.0 is between 0.0 and 9.0

So you need to alter the way you are doing your query:

date >= startdate AND date < enddate_plus_one_day

i.e. in internal date representation tersm if you want yo get the dates that are 6am, i.e. the 9.25 days after 1 jan 1900, then you need to be querying date >= 0.0 and date < 10.0 - this will return the records all the way up to and including 23:59:59

---

I also complained at your querying style - youre passing dates as strings and hoping that sqlserver will figure you out. Don't do this. Be explicit:

SELECT  *
FROM ProjectDetails 
where SubmittedDate >= CONVERT(datetime, @Year, 112)--pass date as string in yyyymmdd
  AND SubmittedDate < (CONVERT(datetime, @Rtype, 112)+1.0) --pass date as a string in yyyymmdd

Between clause on Array of numerical values in row for JSON type column

You may use an APPLY operator and an OPENJSON() call to parse the stored JSON and apply the appropriate WHERE clause:

SELECT * 
FROM (VALUES (N'[{"key": 12}, {"key": 13}, {"key": 19}]')) v (JsonData)
CROSS APPLY OPENJSON(v.JsonData) WITH ([key] int '$.key') j
WHERE j.[key] BETWEEN 5 AND 12

SQL between date range on string date

The best long term solution here would be to make your DATE_col a bona fide date/timestamp type column. That being said, if you must cope with your current design, then consider the answer below.

You should be calling CONVERT in the WHERE clause, when comparing your text date column against date literals. Also, use YYYYMMDD date literal format to be unambiguous as to what you mean:

SELECT *
FROM DW.table_name 
WHERE CONVERT(DATE, DATE_col, 101) >= '20200106' AND
      CONVERT(DATE, DATE_col, 101) <  '20210101';

Note that if you are using a recent version of SQL Server, you could also use TRY_CONVERT here:

TRY_CONVERT(DATE, DATE_col)

Also, I phrased the logic in the WHERE clause to include all dates starting with 6th January, 2020, until the end of 2020. This seems to be what you want here.

---

Related Topics