How to create a prompt to get previous month

[system]

Hi There,

I need a create a prompt such that, if a report runs every 2nd of the month. it supposed to pick previous month (start date & end date) as prompt for data retrieval.

How to achieve this requirement? Database is Oracle.

Thanks in advance.

---

Praveen8367 (BOB member since 2010-11-27)

[system]

Hi,

This sticky topic can help you:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Marek Chladny (BOB member since 2003-11-27)

[system]

Thanks Marek. Will look into this and try to figure out a way…Thanks for your time. Have a goo day!

---

Praveen8367 (BOB member since 2010-11-27)

[system]

you can use a case statment to decide if it’s the 2nd.
and you can use ADD_MONTHS(YOUR_COLUMN,-1) to recieve the date 1 month before the date in “YOUR_COLUMN”

For example:

SELECT ...
FROM ...
WHERE
TABLE.DATES >= CASE WHEN TO_CHAR(SYSDATE,'DD')='02' THEN ADD_MONTHS(@PROMPT('FROM'...),-1) ELSE @PROMPT('FROM'...)  END
AND TABLE.DATES <= CASE WHEN TO_CHAR(SYSDATE,'DD')='02' THEN ADD_MONTHS(@PROMPT('TO'...),-1) ELSE @PROMPT('TO'...)  END

Hope this helps!

---

Dragoran (BOB member since 2009-07-30)