Amazon Redshift : Best way to compare dates

Not the 1st one as it perform unnecessary (unless you really have such unprocessed data) truncate.

 1. trunc(created_at_date) between '2014-06-01' and '2014-06-30'; 

Definitely not this one for obvious reasons (like)

 2. created_at_date like '2014-06%' 

May be this one:

 3. created_at_date >= '2014-06-01 00:00:00' and created_at_date <= '2014-06-30 23:59:59' 

However, since the requirement is to generate monthly reports which I would assume to be a recurring task and on multiple data sources, I would suggest creating a one time calendar table.

This table would have mapping of a date to a month value and then you can simply join your source data with that table and group by the "month" column.

P.S. Just realized I replied to a very 'ancient' question :p

http://docs.aws.amazon.com/redshift/latest/dg/r_DATE_CMP.html

select caldate, '2008-01-04', date_cmp(caldate,'2008-01-04') from date

How about

created_at_date between to_date('20140601','YYYYMMDD') and to_date('20140630','YYYYMMDD') 

I prefer a fourth way:

4. date_trunc('month', created_at_date) = '2014-06-01'

I would suggest not to use the second one (with like) at least... For readability and mostly because it reads naturally (at least to me), i usually use some thing like

select col1, col2 , datepart(d, created_at_date) , datepart(mm, created_at_date) from ... group by datepart(d, created_at_date) , datepart(mm, created_at_date) 

If you want to filter by a particular month or year stick in a where datepart(mm, created_at_date) = 6

have a look at http://docs.aws.amazon.com/redshift/latest/dg/r_Dateparts_for_datetime_functions.html