BETWEEN

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

A BETWEEN condition tests expressions for inclusion in a range of values, using the keywords BETWEEN and AND. (Redshift SQL Language Reference BETWEEN condition)

Grammar Syntax

expression [ NOT ] BETWEEN expression AND expression

This function is fully supported by Snowflake.

Sample Source Patterns

Setup Table

Redshift

CREATE TABLE sales (
    id INTEGER IDENTITY(1,1),
    price FLOAT,
    departmentId INTEGER,
    saleDate DATE
);

INSERT INTO sales (price, departmentId, saleDate) VALUES
(5000, 1, '2008-01-01'),
(8000, 1, '2018-01-01'),
(5000, 2, '2010-01-01'),
(7000, 3, '2010-01-01'),
(5000, 1, '2018-01-01'),
(4000, 4, '2010-01-01'),
(3000, 4, '2018-01-01'),
(9000, 5, '2008-01-01'),
(7000, 5, '2018-01-01'),
(6000, 5, '2006-01-01'),
(5000, 5, '2008-01-01'),
(5000, 4, '2018-01-01'),
(8000, 3, '2006-01-01'),
(7000, 3, '2016-01-01'),
(2000, 2, '2018-01-01');

Snowflake

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT COUNT(*) FROM sales
WHERE departmentId BETWEEN 2 AND 4;

SELECT * FROM sales
WHERE departmentId BETWEEN 4 AND 2;

SELECT * FROM sales
WHERE departmentId NOT BETWEEN 4 AND 2;

SELECT * FROM sales
WHERE departmentId BETWEEN 2 AND 4
AND saleDate BETWEEN '2010-01-01' and '2016-01-01';

select 'some ' between c_start and c_end 
from( select 'same' as c_start, 'some' as c_end );

Results

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT COUNT(*) FROM
    sales
WHERE departmentId BETWEEN 2 AND 4;

SELECT * FROM
    sales
WHERE departmentId BETWEEN 4 AND 2;

SELECT * FROM
    sales
WHERE departmentId NOT BETWEEN 4 AND 2;

SELECT * FROM
    sales
WHERE departmentId BETWEEN 2 AND 4
AND saleDate BETWEEN '2010-01-01' and '2016-01-01';

select
    RTRIM( 'some ') between c_start and c_end
from( select 'same' as c_start, 'some' as c_end );

Results

Known Issues

No issues were found.

Related EWIs

No related EWIs.