Does Ms SQL Server's "Between" Include the Range Boundaries

Does MS SQL Server's between include the range boundaries?

The BETWEEN operator is inclusive.

From Books Online:

BETWEEN returns TRUE if the value of
test_expression is greater than or
equal to the value of begin_expression
and less than or equal to the value of
end_expression.

DateTime Caveat

NB: With DateTimes you have to be careful; if only a date is given the value is taken as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day's data at midnight in multiple ranges, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.

e.g. to get all values within June 2016 you'd need to run:

where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')

i.e.

where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'

datetime2 and datetimeoffset

Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:

where myDateTime >= '20160601' AND myDateTime < '20160701'

SQL between not inclusive

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.

SQL query records within a range of boundaries and max/min outside the range

As you wish, without UNION.

MySQL (TESTED)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp < '@START_DATE' 
           ORDER BY dv2.timestamp DESC 
           LIMIT 1
           )
    AND ( SELECT dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp > '@END_DATE' 
          ORDER BY dv3.timestamp ASC 
          LIMIT 1
        )

EDIT Sorry, I forgot to notice about T-SQL.

T-SQL (NOT TESTED)

SELECT 
     dv1.timestamp, dv1.values
FROM 
     myTable AS dv1
WHERE 
    dv1.timestamp 
    BETWEEN (
           SELECT TOP 1 dv2.timestamp 
           FROM myTable AS dv2
           WHERE dv2.timestamp >  @START_DATE 
           ORDER BY dv2.timestamp DESC
           )
    AND ( SELECT TOP 1 dv3.timestamp 
          FROM myTable AS dv3
          WHERE dv3.timestamp <  @END_DATE 
          ORDER BY dv3.timestamp ASC
        )

Note If the result is not right, you could just exchange the sub queries (i.e. operators, and ASC/DESC).

Think out of the box :)

SQL : BETWEEN vs = and =

They are identical: BETWEEN is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009').

Use an alternative longer syntax where BETWEEN doesn't work because one or both of the values should not be included e.g.

Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'

(Note < rather than <= in second condition.)

Using between clause on nvarchar column to find range is not working

The issue is with your use of BETWEEN as per the docs:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

Because you don't know whether USER_3 or USER_4 is the higher limit or the lower limit, you need to test both ways.

Note: For this sort of query I prefer to pre-calculate all the values (using CROSS APPLY in this case) I need. It makes it much easier to follow and debug.

SELECT USER_3, USER_4, [DESCRIPTION]

    , CASE WHEN ISNUMERIC([DESCRIPTION]) <> 1 THEN 'Y' ELSE
        CASE WHEN CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END BETWEEN CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END AND
        CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3  AS decimal(10,5)) ELSE 0 END
    THEN 'N' ELSE 'Y' END END AS Flagset

    , CASE WHEN DNUMERIC <> 1 THEN 'Y' ELSE CASE WHEN DESCRIPTIOND BETWEEN USER_4D AND USER_3D OR DESCRIPTIOND BETWEEN USER_3D AND USER_4D THEN 'N' ELSE 'Y' END END CorrectedFlagSet

FROM (VALUES
    ('1.395','1.385','1.390')
    , ('22.025','41.425','22')
    , ('22.025','41.425','23.025')
) AS X (USER_3, USER_4, [DESCRIPTION])
CROSS APPLY (VALUES (
    CASE WHEN ISNUMERIC(USER_3) = 1 THEN CAST(USER_3 AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC(USER_4) = 1 THEN CAST(USER_4 AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN CAST([DESCRIPTION] AS decimal(10,5)) ELSE 0 END
    , CASE WHEN ISNUMERIC([DESCRIPTION]) = 1 THEN 1 ELSE 0 END
)) AS Y (USER_3D, USER_4D, DESCRIPTIOND, DNUMERIC);

Returns:

CURDATE() BETWEEN start_date AND DATE_ADD(end_date, INTERVAL 1 DAY)

 where (x not between x1 and x2) or (y not between y1 and y2)

 where not (x between x1 and x2 and y between y1 and y2)