Toad World® Forums

Automate 'between to date'

Hi,

I'm trying to schedule an automatic weekly query...

Select...from...where...between_to_date...and to_date

How can I set it up so that the date range will refresh to between 'the first day of the current month' and 'the current date'? Or if simpler, 'the first day of the current month' and 'the first day of the next month'?

Thanks!
Apologies if this is super easy and has been covered elsewhere - I'm really new to Toad for Oracle :slight_smile:

this expression will get you first day of the current month:

Add_Months(trunc(last_day(sysdate)) + 1, -1)

So, this will get you all rows where "Column" has a value between first of month and right now.

select * 
from table
where column between Add_Months(trunc(last_day(sysdate)) + 1, -1) and sysdate
1 Like

Morning All,

the first day of the current month is:

select trunc(sysdate, 'MM') from dual;
01/02/2020

The first of next month is:

select trunc(add_months(sysdate, 1), 'MM') from dual;
01/03/2020

The last day of this month is:

select trunc(add_months(sysdate, 1), 'MM') -1 from dual;
29/02/2020

So, your SQL for between the first of the month and this morning at will be something like:

select ... from ... where some_date_column between trunc(sysdate, 'MM') and trunc(sysdate);

If you want between the first of the month and right this very second, the it will be:

select ... from ... where some_date_column between trunc(sysdate, 'MM') and sysdate;

If you want the first day of this month and the first day of next month:

select ... from ... where some_date_column between trunc(sysdate, 'MM') and trunc(sysdate);

If you want only this month, from the first second to the very last second:

select ... from ... where some_date_column between trunc(sysdate, 'MM') and (trunc(add_months(sysdate, 1), 'mm')) -1/24/60/60;

HTH

Cheers,
Norm. [TeamT]

2 Likes