pulling data from current date back to 1st of the month

[adrock]

hi,

i want to automate a query that pulls data from the current date back through the first of the month. i want it to run every day.

something like:

select colname from table where createdate >= {first_of_the_month} and createdate <= sysdate;

how can automate first_of_the_month without additional processing? i see a way i could do it from now til end of month with last_day(), but nothing like first_day.

thanks!

[stecal]

last day of the month prior plus one day?

[adrock]

oh, you mean something like

last_day(add_months(sysdate, -1)) + 1

will add_months(sysdate, -1) always give me the prior month ?

[marist89]

Yup

[jmodic]

Instead of using two functions (LAST_DAY + ADD_MONTHS) you can simply get it by using TRUNC function:

TRUNC(sysdate,'MM')