Working in Oracle:

 

I had a project the other day where on the first of every month, I needed to grab all entries from the month before.

The Oracle function LAST_DAY comes in handy right here. I'm sure there are many ways to deal with this, I came up with 2 ways:

 

1: (BEST)

1. SELECT *
2. FROM mytable
3. WHERE SomeDate BETWEEN TRUNC(SYSDATE , 'month') AND LAST_DAY(TRUNC(SYSDATE ))+1

 

2: (WORKS, BUT NOT BEST)

1. SELECT *
2. FROM mytable
3. WHERE TRUNC(SomeDate) BETWEEN TRUNC(SYSDATE, 'month') AND LAST_DAY(TRUNC(SYSDATE + 1))

 

The difference in these 2 queries is that in #1 you are not executing the trunc function on the left side of the where clause therefore, your cost or expense to run the query is less than that of #2 where we are using the trunc function on the left side. Anytime you can avoid using a function on the left side of a where clause is good practice, that way your query executes much faster.

In #1, because we are trunc'ing the right side of the where clause, we can add 1 day to the last day of the month, which is really giving us a day of ex:  (10-01-2009 12:00am). So essentially we are looking for the date to be between ex: (09-01-2009 12:00am) AND (09-30-2009 11:59pm).