oracle SQL 日期公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/240341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Date Formulas
提问by
I need a date formula in Oracle SQL or T-SQL that will return a date of the previous week (eg Last Monday's date).
我需要 Oracle SQL 或 T-SQL 中的日期公式来返回前一周的日期（例如上周一的日期）。
I have reports with parameters that are run each week usually with parameter dates mon-friday or sunday-saturday of the previous week. I'd like to not have to type in the dates when i run the reports each week.
我有每周运行的参数报告，参数日期通常为前一周的周一至周五或周日至周六。我不想在每周运行报告时输入日期。
The data is in Oracle and I am using SQL Server 2005 Reporting Services (SSRS) for the reports.
数据在 Oracle 中，我使用 SQL Server 2005 Reporting Services (SSRS) 来生成报告。
回答by Tomalak
T-SQL:
T-SQL：
SELECT
DateColumn,
DateColumn - CASE DATEPART(dw, DateColumn)
WHEN 1 THEN 6
ELSE DATEPART(dw, DateColumn) - 2
END MondayOfDateColumn
FROM
TheTable
Do you need the time part to be "00:00:00", too?
您是否也需要时间部分为“00:00:00”？
If so, add this expression to the calculation:
如果是，请将此表达式添加到计算中：
DATEADD(dd, 0, DATEDIFF(dd, 0, DateColumn)) - CASE DATEPART(dw, /* etc. etc. */
回答by Leigh Riffel
Here is an Oracle solution for Monday.
这是周一的 Oracle 解决方案。
select sysdate - 5 - to_number(to_char(sysdate,'D')) from dual
Here are examples that retrieve any particular day from the previous week.
以下是从上周检索任何特定日期的示例。
SELECT sysdate - 6 - to_number(to_char(sysdate,'D')) LastSunday FROM dual;
SELECT sysdate - 5 - to_number(to_char(sysdate,'D')) LastMonday FROM dual;
SELECT sysdate - 4 - to_number(to_char(sysdate,'D')) LastTuesday FROM dual;
SELECT sysdate - 3 - to_number(to_char(sysdate,'D')) LastWednesday FROM dual;
SELECT sysdate - 2 - to_number(to_char(sysdate,'D')) LastThursday FROM dual;
SELECT sysdate - 1 - to_number(to_char(sysdate,'D')) LastFriday FROM dual;
SELECT sysdate - 0 - to_number(to_char(sysdate,'D')) LastSaturday FROM dual;
If you need the time part to be 00:00:00 wrap the statment in TRUNC(...).
如果您需要时间部分为 00:00:00，请将语句包装在 TRUNC(...) 中。
回答by Noah Yetter
(Oracle)
(甲骨文)
trunc(sysdate,'IW') --gives this week's monday
trunc(sysdate,'IW') -- 给出本周的星期一
trunc(sysdate,'IW')-7 --gives last week's monday
trunc(sysdate,'IW')-7 --给出上周的星期一
This assumes you consider monday to be the first day of the week, which is what 'IW' (ISO Week) signifies. If you consider sunday to be the first day of the week...
这假设您将星期一视为一周的第一天，这就是“IW”（ISO 周）的含义。如果您认为星期日是一周的第一天...
trunc(sysdate,'W')+1 --gives this week's monday, on sunday this will be in the future
trunc(sysdate,'W')+1 -- 给出本周的星期一，星期天这将是未来
trunc(sysdate,'W')+1-7 --gives last week's monday
trunc(sysdate,'W')+1-7 --给出上周的星期一
回答by Amy B
Here's my solution, tested against 8 days.
这是我的解决方案，经过 8 天的测试。
SET DateFirst 7
DECLARE @Today datetime
SET @Today = '2008-10-22'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-23'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-24'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-25'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-26'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-27'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-28'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
SET @Today = '2008-10-29'
SELECT DateAdd(wk, DateDiff(wk, 0, DateAdd(dd, -1, @Today)) - 1, 0) as PreviousMonday, @Today as Today
Here's the trouble with Sunday:
这是星期天的麻烦：
SELECT
DateDiff(wk, 0, '2008-10-25') as SatWeek, --5677
DateDiff(wk, 0, '2008-10-26') as SunWeek, --5688
DateDiff(wk, 0, '2008-10-27') as MonWeek --5688
SELECT
DatePart(dw, '2008-10-25') as SatPart, --7
DatePart(dw, '2008-10-26') as SunPart, --1
DatePart(dw, '2008-10-27') as MonPart, --2
convert(datetime,'2008-10-25') - (DatePart(dw, '2008-10-25') - 2) as SatMonday,
--'2008-10-20'
convert(datetime,'2008-10-26') - (-1) as SunMonday,
--'2008-10-27'
convert(datetime,'2008-10-27') - (DatePart(dw, '2008-10-27') - 2) as MonMonday
--'2008-10-27'
Many of these solutions Provide the same answer for Sunday and Monday in the same week. The old Monday should not be resigned until another Monday has occurred.
许多这些解决方案为同一周的周日和周一提供相同的答案。在另一个星期一发生之前，旧的星期一不应被放弃。
回答by StingyHyman
回答by JosephStyons
In Oracle:
在甲骨文中：
Edit: Made it a bit more concise
编辑：使它更简洁一点
Edit: Leigh Riffel has posted a much better solution than mine.
编辑：Leigh Riffel 发布了一个比我更好的解决方案。
select
case when 2 = to_char(sysdate-1,'D') then sysdate - 1
when 2 = to_char(sysdate-2,'D') then sysdate - 2
when 2 = to_char(sysdate-3,'D') then sysdate - 3
when 2 = to_char(sysdate-4,'D') then sysdate - 4
when 2 = to_char(sysdate-5,'D') then sysdate - 5
when 2 = to_char(sysdate-6,'D') then sysdate - 6
when 2 = to_char(sysdate-7,'D') then sysdate - 7
end as last_monday
from dual
回答by Tom H
A T-SQL solution:
T-SQL 解决方案：
Assuming that SET DATEFIRST is at the default (Sunday = 7), last Monday's date:
假设 SET DATEFIRST 为默认值（周日 = 7），即上周一的日期：
SELECT
DATEADD(dy, DATEPART(dw, GETDATE()) - 9, GETDATE())
The "-9' is to go back one week (-7) and then since Monday is 2 we are subtracting 2 more and adding the day of the week for the current day.
“-9”是返回一周（-7），然后由于星期一是 2，我们再减去 2 并添加当天的星期几。
