Redshift query between date

sql amazon-redshift

newleaf · Jul 13, 2017 · Viewed 27k times · Source

I'm quite new to Redshift SQL.

    select *  from myredshift_tbl 
    where local_date between \'2016-01-01\' and \'2017-02-01\';

But got this error:

[amazon][500310] invalid operation syntax error at or near "\". I believe Redshift use single quote and I need to escape single quote.

Answer

If the column local_date is in date format, use:

select *  from myredshift_tbl 
    where local_date between '2016-01-01' and '2017-02-01';

If the column local_date is timestamp:

select *  from myredshift_tbl 
        where local_date between '2016-01-01 00:00:00' and '2017-02-01 23:59:59';