代码之家 › 专栏 › 技术社区 › syed mohsin

SQL Server上周从sun到sat

sql-server-2014 datetime tsql sql-server sql

1

syed mohsin · 技术社区 · 7 年前

我想从上个星期的太阳到周六的日期我的代码是,

Declare
      @now DateTime, @fmWeek DateTime, @toWeek DateTime

Set @now = GetDate()
Set @fmWeek = Convert(DateTime, DateAdd(wk, DateDiff(wk, 0, @now), -1))
Set @toWeek = DATEADD(ms,-3,Convert(DateTime, DateAdd(wk, DateDiff(wk, 0,  @now), 6)))

select @fmWeek, @toWeek

它显示的数据来自 2018-04-15 00:00:00.000 到 2018-04-21 23:59:59.997

我需要得到前一周太阳到周六的日期,而不是同一周

2 回复 | 直到 7 年前

1

2

Lukasz Szozda 7 年前

我需要得到前一周太阳到周六的日期,而不是同一周

我只想减去7天:

Declare @now DateTime = GetDate()
    ,@fmWeek DateTime, @toWeek DateTime;

Set @fmWeek = DateAdd(wk, DateDiff(wk, 0, @now), -1) - 7;
Set @toWeek = DateAdd(wk, DateDiff(wk, 0, @now), 5) - 7;
select @fmWeek, @toWeek;

DBFiddle Demo

2

0

PSK 7 年前

如果您只想显示两个日期,@lad2025已经为您提供了正确的查询来获取日期。

如果您想在这两天内显示所有日期,可以尝试如下操作。

Declare @now DateTime = GetDate()
    ,@fmWeek DateTime, @toWeek DateTime;

Set @fmWeek = DateAdd(wk, DateDiff(wk, 0, @now), -1) - 7;
Set @toWeek = DateAdd(wk, DateDiff(wk, 0, @now), 5) - 7;

SELECT @fmWeek + RN AS DATE FROM
(   
    SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
) T 
WHERE RN <= DATEDIFF(DAY,@fmWeek,@toWeek)

输出:

Date
------------------------
2018-04-08 00:00:00.000
2018-04-09 00:00:00.000
2018-04-10 00:00:00.000
2018-04-11 00:00:00.000
2018-04-12 00:00:00.000
2018-04-13 00:00:00.000
2018-04-14 00:00:00.000