Stupid Oracle SQL Tricks, part 2

grouping dates by week:
to_char(my_date + (1-to_char(my_date,'D')),'YYYY-MM-DD') Week_Of_My_Date
You're basically subtracting the day of the week and ending up with Sunday for any day in a given week.

finding records by today's date or last month or two or three months ago:
select count(my_whatever) from my_table t where t.my_date > add_months(current_date, -2) and t.my_date <= current_date
Oracle uses sysdate for today, which is really common and great and all but I've learned that almost all database systems (really all but SQL Server) support current_date, which is more descriptive and works in more places, FTW!


the median (middle) item of a list:
select PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY my_item DESC) median_item from my_table
This is slightly more complicated than just the 'median' (meaning it may not do what you think its doing) so to understand further take a look at the Oracle manual.