SQL 日期公式

Question

我需要 Oracle SQL 或 T-SQL 中的日期公式来返回上周的日期（例如上周一的日期）。

我的报告包含每周运行的参数，通常参数日期为前一周的周一至周五或周日至周六。 我不想在每周运行报告时输入日期。

数据位于 Oracle 中，我使用 SQL Server 2005 Reporting Services (SSRS) 来生成报告。

Answer 1

T-SQL：

SELECT 
  DateColumn,
  DateColumn - CASE DATEPART(dw, DateColumn) 
                WHEN 1 THEN 6
                ELSE DATEPART(dw, DateColumn) - 2
              END MondayOfDateColumn
FROM 
  TheTable

您是否也需要时间部分为“00:00:00”？

如果是这样，请将此表达式添加到计算中：

DATEADD(dd, 0, DATEDIFF(dd, 0, DateColumn)) - CASE DATEPART(dw, /* etc. etc. */

Answer 2

这是周一的 Oracle 解决方案。

select sysdate - 5 - to_number(to_char(sysdate,'D')) from dual

以下是检索上周任何特定日期的示例。

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;

如果您需要时间部分为 00:00:00，请将语句包装在 TRUNC(...) 中。

Answer 3

查看这篇文章中的日期函数列表。 你想要这个。

SELECT (DATEADD(wk,DATEDIFF(wk,0,GETDATE()) -1 ,0))

它们几乎总是数学而不是面向字符串的，因此它们比套管或铸造操作工作得更快

Answer 4

这是我的解决方案，经过 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

这是周日的问题：

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'

许多解决方案为同一周的周日和周一提供相同的答案。 旧的星期一不应该被放弃，直到另一个星期一发生。

Answer 5

(Oracle)

trunc(sysdate,'IW') --给出本周的星期一

trunc(sysdate,'IW')-7 --给出上周的星期一

这假设您认为星期一是一周的第一天，这就是“IW”（ISO 周）的意思。 如果您认为星期日是一周的第一天...

trunc(sysdate,'W')+1 --给出本周的星期一，星期日这将是未来的

trunc(sysdate,'W')+1 -7 --给出上周的星期一

Answer 6

在 Oracle 中：

编辑：使其更加简洁

编辑：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

Answer 7

T-SQL 解决方案：

假设 SET DATEFIRST 为默认值（周日 = 7），上周一的日期：

SELECT
DATEADD(dy, DATEPART(dw, GETDATE()) - 9, GETDATE())

“-9”表示返回一周 (-7)，然后由于周一为 2，我们再减去 2并添加当天是星期几。