如何在oracle10g中不使用日期函数获取上一个月的最后一天

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

如何在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

但我想要不使用日期功能

2 个解决方案

#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.

你必须调整日期函数才能在oracle中工作。但这样可以让您在一次性加载后没有数据库特定功能的情况下执行日期逻辑。

#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.

这种形式的trunc()函数为您提供日期“将当天的时间部分截断为格式模型指定的单位”,尽管这有点误导,因为您不限于时间格式元素。这里我使用了格式模型'MM',因此它截断到当前月份的第一个。前一天是前一个月的最后一天。

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.

但是trunc()仍然是一个日期函数,具体取决于你的定义;也许更重要的是,它仍然是特定于Oracle的语法,您可能会出于某种原因试图避免这种语法。但是也不允许使用sysdate。

#1