Toad World® Forums

Date Automation

(MB.CRTEDT > ‘’ /Last 30 days/)

OR (BU.TEDT > ‘’ /Last 30 days/)

OR (BU.TEDT >= sysdate - 30)

Hello- I don’t use the query builder bc I prefer to write my code using the editor, so I have a question reagarding automated reports and Dates.

the query currently looks like this:
(MB.CRTEDT >= ‘2011-09-18’ OR BU.TEDT>= ‘2011-09-18’)
to automate this I was thinking of doing this:
However if I do that it will not take the “>=” so I will miss one day

Then I was thinking
(MB.CRTEDT >= sysdate - 30)
but again a month can have 31 or 30 days. I want to automate this report bc it’s a timely query to run. Any help would be greatly appreciated. Thanks.

WHERE (ORDERS.ORDER_DATE = calendar__gregorian__last_month__)

Debbie

FROM QUEST_STAGE.ORDERS ORDERS

The Query Builder has a date filter for last month and it will calculate the correct date range for last month. Use the Query Builder to get the text string that it uses to replace.

This is the one from TDA 2.7 but may be different if you are using an older version of TDA.

SELECT ORDERS.ORDER_DATE

Hi Debbie- that will not allow the “>=”

where DATE_1 >= to_date(ADD_MONTHS(sysdate,-1)) AND DATE_1 <= to_date(ADD_MONTHS(sysdate,1))

Debbie

FROM DEB.DATETEST DATETEST

I am not sure what db type you are using or the excat date range you are trying to get. But what about using ADD_MONTH like this below? On my test table I can get August 15, 2011 in this result set.

SELECT DATETEST.DATE_1

When using sysdate in this fashion, it’s important to remember that sysdate includes the current time, and if you don’t eliminate the time portion of sysdate, you might end up missing records if you say something like DATE_COLUMN >= add_months (sysdate, -1)

First you have to ask yourself: does your date column include the time portion of the date? If so, do you want to just compare dates, or dates AND times?

If you’re just comparing dates, and your date column doesn’t include the time portion of the date, you should add the TRUNC function to the expression from Debbie Peabody’s e-mail. (You can also remove the to_date since add_months returns a date result.)

where DATE_1 BETWEEN add_months(trunc (sysdate, ‘DD’),-1) AND add_months(trunc (sysdate, ‘DD’),1)

LAST_DAY(TO_DATE(TO_CHAR(TRUNC(SYSDATE, ‘MM’) - 1, ‘DD-MON-YYYY’) || ‘23:59:59’, ‘DD-MON-YYYY HH24:MI:SS’))

How about this:

MB.CRTEDT between
–First day of the previous month.
TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, - 1), ‘MM’), ‘DD-MON-YYYY’) || ‘00:00:00’, ‘DD-MON-YYYY HH24:MI:SS’)

AND

– Last day of the previous month