Redshift query between date

Question

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.

Please consider accepting an answer if you are satisfied with it. It will help other users who find this question. — San
– San, Commented Jan 31, 2018 at 16:51

Yusuf Hassan · Accepted Answer · 2017-08-10 12:34:36Z

39

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';

answered Aug 10, 2017 at 12:34

Yusuf Hassan

2,0131 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

What happens if the lower and upper bound are reversed?

Golokesh Patra · Accepted Answer · 2017-08-17 12:30:21Z

5

SELECT * FROM schemaName.TableName WHERE datetime > '2017-02-09 
00:00:00' AND datetime < '2017-06-09 00:00:00';

The above query Works with Redshift to fetch all the entries in a table.

NOTE: The table I applied the query on had column/field 'datetime' of type 'timestamp'.

I tested this query on Redshift with the help of Workbench J.

answered Aug 10, 2017 at 6:43

Golokesh Patra

59810 silver badges29 bronze badges