how to get previous month of last day without using date function in oracle10g

Question

How to get previous month of last day without using date function in oracle?

SQL> ed
Wrote file afiedt.buf

  1  SELECT LAST_DAY(ADD_MONTHS(sysdate,-1))
  2* from dual
SQL> /

LAST_DAY(
---------
30-JUN-14

But I want without using date function

Answer 1

Create a calendar table.

Example here:

http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

Also this question:

Calendar table for Data Warehouse

You'll have to adjust the date functions to work in oracle. But this will let you do date logic without the database specific functions after the one-time load.

Answer 2

You haven't specified which functions you can and can't use, or why, so I don't know if this is allowed by whatever constraints you're trying to do this under:

select trunc(sysdate, 'MM') - 1
from dual;

TRUNC(SYSDATE,'MM')-1
---------------------
30-JUN-14             

This form of the trunc() function gives you your date "with the time portion of the day truncated to the unit specified by the format model", although that's slightly misleading as you are not restricted to time format elements. Here I've used format model 'MM', so it truncates to the first of the current month. One day before that is the last day of the previous month.

But trunc() is still a date function, depending on your definition; and maybe more importantly it's still Oracle-specific syntax, which you might be trying to avoid for some reason. but then using sysdate wouldn't be allowed either.