SQL Query to Retrieve Monthly Data

Question

I'm working with the following SQL Query in Redash, the query retrieves monthly data from table.

SELECT *
FROM Table 
WHERE
"Date" between '2021-04-01T00:00:00.669976+00:00' and '2021-04-30T23:59:59.669976+00:00'

I'd like to know if there's a workaround to updating the WHERE clause in an efficient manner rather than manually typing it out at the end of each month.

Answer 1

This worked well for me:

WHERE
EXTRACT(MONTH FROM  "Date") = EXTRACT(MONTH FROM CURRENT_DATE) AND EXTRACT(YEAR FROM  "Date") = EXTRACT(YEAR FROM CURRENT_DATE)

Answer 2

In your case, I suggest you avoid any solution that involves doing a convert or other type of conversion with the GRP_Date field. By doing that, you do not allow SQL Server to be able to use an index if there is one for the GRP_Date field and this can affect your performance in a very obvious way.

And of course between is not ideal in this specific case for the reasons already mentioned in your question.

I suggest the following condition for the best performance (good use of the indexes) and to avoid problems with the hours

where GRP.GRP_date >= @since
and GRP.GRP_date < dateadd(day, 1, @until) -- @until + 1 day

In the case where:

• @since = 2016-11-01

• @until = 2016-11-14

where GRP.GRP_Fecha >= '2016-11-01'
and GRP.GRP_Fecha < '2016-11-15' 

When ordering dates before 2016-11-15, this includes all dates from 2016-11-14 regardless of time.