DB2到Oracle转换的基本日期时间列之间的子句

What this is doing is selecting all columns from TABLE where a specific date time column is between last Sunday and this coming Saturday, 7 days total (no matter what day of the week you are running the query on)

这样做是从TABLE中选择所有列,其中特定日期时间列在上周日和即将到来的星期六之间,总共7天(无论您运行查询的一周中的哪一天)

I would like to have help converting the below statement into Oracle since I found out that it will not work on Oracle.

我想帮助将以下语句转换为Oracle,因为我发现它不适用于Oracle。

SELECT *
FROM TABLE
WHERE DATE_TIME_COLUMN
BETWEEN
current date - ((dayofweek(current date))-1) DAYS
AND
current date + (7-(dayofweek(current date))) DAYS

3 个解决方案

#1

After poking around a bit more I was able to find something that worked for my specific problem with no administrator restrictions for whatever reason:

在更多地探索之后,我能够找到适用于我的特定问题的东西,无论出于何种原因没有管理员限制:

SELECT *
FROM TABLE
WHERE DATE_TIME_COLUMN
BETWEEN
    TIMESTAMPADD(SQL_TSI_DAY, DayOfWeek(Current_Date)*(-1) + 1, Current_Date)
AND
    TIMESTAMPADD(SQL_TSI_DAY, 7 - DayOfWeek(Current_Date), Current_Date)

#2

Use TRUNC() to truncate to the start of the week:

使用TRUNC()截断到一周的开头:

 SELECT *
   FROM TABLE
  WHERE DATE_TIME_COLUMN
BETWEEN trunc(sysdate, 'WW')
    and 
        trunc(sysdate + 7, 'WW');

sysdate is the current system date, trunc truncates a data, and WW tells it to truncate to the week (rather than day, year, etc.).

sysdate是当前系统日期,trunc截断数据,WW告诉它截断到一周(而不是日,年等)。

#3

Assuming DATE_TIME_COLUMN is - as it should be - of datatype date, I think this gets what you want.

假设DATE_TIME_COLUMN是 - 应该是 - 数据类型日期,我想这会得到你想要的。

where DATE_TIME_COLUMN between
next_day(sysdate,'SUNDAY')-7 and next_day(sysdate,'SATURDAY')

You may need to tweak it a bit. Please follow up studying the official docs on the NEXT_DAY function in the relevant docs at http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions106.htm#SQLRF00672

您可能需要稍微调整一下。请通过http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions106.htm#SQLRF00672跟进相关文档中NEXT_DAY函数的官方文档。

The proposed TRUNC does not guarantee you get the date of a particular day of the week:

SQL> alter session set nls_date_format='day dd-mon-yyyy';

Session altered.

SQL> select trunc(sysdate,'WW') from dual;

TRUNC(SYSDATE,'WW')
---------------------
friday    22-jan-2016

SQL> select trunc(sysdate+7,'WW') from dual;

TRUNC(SYSDATE+7,'WW')
---------------------
friday    29-jan-2016

#1