Câu hỏi Nhận ngày đầu tiên trong tuần trong SQL Server


Tôi đang cố gắng nhóm các bản ghi theo tuần, lưu trữ ngày tổng hợp như ngày đầu tiên của tuần. Tuy nhiên, kỹ thuật tiêu chuẩn tôi sử dụng để làm tròn ngày không có vẻ hoạt động chính xác với các tuần (mặc dù nó cho ngày, tháng, năm, quý và bất kỳ khung thời gian nào khác mà tôi đã áp dụng).

Đây là SQL:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), 0);

Điều này trả về 2011-08-22 00:00:00.000, thứ hai, không phải là chủ nhật. Lựa chọn @@datefirst trả về 7, đó là mã cho ngày chủ nhật, vì vậy máy chủ được thiết lập chính xác theo như tôi biết.

Tôi có thể bỏ qua điều này một cách dễ dàng bằng cách thay đổi mã trên thành:

select "start_of_week" = dateadd(week, datediff(week, 0, getdate()), -1);

Nhưng thực tế là tôi phải làm một ngoại lệ như vậy làm cho tôi hơi khó chịu. Ngoài ra, xin lỗi nếu đây là câu hỏi trùng lặp. Tôi đã tìm thấy một số câu hỏi liên quan nhưng không có câu hỏi nào giải quyết vấn đề này một cách cụ thể.


76
2017-08-23 23:50


gốc


(@@DATEFIRST + DATEPART(DW, @SomeDate)) % 7 vẫn không đổi bất kể @@datefirst tôi nghĩ. Với Thứ Hai = 2. - Martin Smith


Các câu trả lời:


Để trả lời lý do tại sao bạn nhận được một thứ hai và không phải là một chủ nhật:

Bạn đang thêm một số tuần vào ngày 0. Ngày 0 là gì? 1900-01-01. Ngày nào là ngày 1900-01-01? Thứ hai. Vì vậy, trong mã của bạn, bạn đang nói, bao nhiêu tuần đã trôi qua kể từ thứ Hai, ngày 1 tháng 1 năm 1900? Hãy gọi đó là [n]. Ok, bây giờ thêm [n] tuần đến thứ hai, ngày 1 tháng 1 năm 1900. Bạn không nên ngạc nhiên rằng điều này kết thúc là một thứ hai. DATEADD không có ý tưởng rằng bạn muốn thêm tuần nhưng chỉ cho đến khi bạn nhận được một chủ nhật, nó chỉ là thêm 7 ngày, sau đó thêm 7 ngày nữa, ... giống như DATEDIFF chỉ nhận ra ranh giới đã bị vượt qua. Ví dụ, cả hai trả về 1, mặc dù một số người phàn nàn rằng cần có một số logic hợp lý được xây dựng để làm tròn lên hoặc xuống:

SELECT DATEDIFF(YEAR, '2010-01-01', '2011-12-31');
SELECT DATEDIFF(YEAR, '2010-12-31', '2011-01-01');

Để trả lời làm thế nào để có được một chủ nhật:

Nếu bạn muốn một ngày chủ nhật, sau đó chọn một ngày cơ sở không phải là một ngày thứ hai mà là một ngày chủ nhật. Ví dụ:

DECLARE @dt DATE = '1905-01-01';
SELECT [start_of_week] = DATEADD(WEEK, DATEDIFF(WEEK, @dt, CURRENT_TIMESTAMP), @dt);

Điều này sẽ không phá vỡ nếu bạn thay đổi DATEFIRST cài đặt (hoặc mã của bạn đang chạy cho người dùng có cài đặt khác) - miễn là bạn vẫn muốn có Chủ nhật bất kể cài đặt hiện tại. Nếu bạn muốn hai câu trả lời đó là jive, thì bạn nên sử dụng một hàm làm Phụ thuộc vào DATEFIRST cài đặt, ví dụ:

SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP);

Vì vậy, nếu bạn thay đổi DATEFIRST thiết lập để thứ hai, thứ ba, những gì có bạn, hành vi sẽ thay đổi. Tùy thuộc vào hành vi bạn muốn, bạn có thể sử dụng một trong các chức năng sau:

CREATE FUNCTION dbo.StartOfWeek1 -- always a Sunday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @d), '19050101'));
END
GO

...hoặc là...

CREATE FUNCTION dbo.StartOfWeek2 -- always the DATEFIRST weekday
(
    @d DATE
)
RETURNS DATE
AS
BEGIN
    RETURN (SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, @d), @d));
END
GO

Bây giờ, bạn có nhiều lựa chọn thay thế, nhưng cái nào hoạt động tốt nhất? Tôi sẽ ngạc nhiên nếu có bất kỳ sự khác biệt lớn nhưng tôi đã thu thập tất cả các câu trả lời được cung cấp cho đến nay và chạy chúng thông qua hai bộ kiểm tra - một trong những giá rẻ và đắt tiền. Tôi đo số liệu thống kê khách hàng bởi vì tôi không thấy I / O hoặc bộ nhớ đóng một phần trong hiệu suất ở đây (mặc dù những người có thể đi vào chơi tùy thuộc vào cách chức năng được sử dụng). Trong các thử nghiệm của tôi, kết quả là:

Truy vấn gán "giá rẻ":

Function - client processing time / wait time on server replies / total exec time
Gandarez     - 330/2029/2359 - 0:23.6
me datefirst - 329/2123/2452 - 0:24.5
me Sunday    - 357/2158/2515 - 0:25.2
trailmax     - 364/2160/2524 - 0:25.2
Curt         - 424/2202/2626 - 0:26.3

Truy vấn gán "đắt tiền":

Function - client processing time / wait time on server replies / total exec time
Curt         - 1003/134158/135054 - 2:15
Gandarez     -  957/142919/143876 - 2:24
me Sunday    -  932/166817/165885 - 2:47
me datefirst -  939/171698/172637 - 2:53
trailmax     -  958/173174/174132 - 2:54

Tôi có thể chuyển tiếp các chi tiết của các bài kiểm tra của tôi nếu muốn - dừng ở đây vì điều này đã nhận được khá dài. Tôi hơi ngạc nhiên khi thấy Curt xuất hiện nhanh nhất ở cấp cao, với số lượng tính toán và mã nội tuyến. Có lẽ tôi sẽ chạy một số bài kiểm tra kỹ lưỡng hơn và blog về nó ... nếu các bạn không có bất kỳ phản đối nào để tôi xuất bản các chức năng của bạn ở nơi khác.


119
2017-08-24 02:01



Vì vậy, nếu tôi xem xét các tuần của tôi để bắt đầu vào chủ nhật và kết thúc vào thứ bảy, tôi có thể nhận được Cuối cùng ngày trong tuần cho bất kỳ ngày nào @d như thế này: CHỌN DATEADD (wk, DATEDIFF (wk, '19041231', @d), '19041231') - Baodad


Đối với những thứ cần có:

Thứ Hai = 1 và Chủ nhật = 7:

SELECT 1 + ((5 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Chủ nhật = 1 và Thứ Bảy = 7:

SELECT 1 + ((6 + DATEPART(dw, GETDATE()) + @@DATEFIRST) % 7);

Trên đây là một ví dụ tương tự, nhưng nhờ có đôi "% 7" nó sẽ chậm hơn nhiều.


11
2018-02-14 16:21



Điều này cũng hoạt động rất tốt để có được số ngày từ đầu tuần là Mặt trời hoặc Thứ Hai. Cảm ơn - Fandango68
Cách khác select (datediff(dd,5,cal.D_DATE)%7 + 1) và select (datediff(dd,6,cal.D_DATE)%7 + 1) - vasja


Điều này làm việc tuyệt vời cho tôi:

TẠO CHỨC NĂNG [dbo]. [StartOfWeek]
(
  @INPUTDATE DATETIME
)
RETURNS DATETIME

NHƯ
BEGIN
  - NÀY không hoạt động trong chức năng.
  - SET DATEFIRST 1 - đặt thứ hai là ngày đầu tiên của tuần.

  DECLARE @DOW INT - để lưu trữ ngày trong tuần
  SET @INPUTDATE = CONVERT (VARCHAR (10), @INPUTDATE, 111)
  SET @DOW = DATEPART (DW, @INPUTDATE)

  - Ma thuật hội tụ của thứ hai đến 1, thứ ba đến 2, v.v.
  - không tôn trọng những gì máy chủ SQL nghĩ về đầu tuần.
  - Nhưng ở đây chúng ta có chủ nhật được đánh dấu là 0, nhưng chúng ta sửa lỗi này sau.
  SET @DOW = (@DOW + @@ DATEFIRST - 1)% 7
  NẾU @DOW = 0 SET @DOW = 7 - sửa chữa chủ nhật

  TRẢ LẠI NGÀY (DD, 1 - @ DOW, @ INPUTDATE)

KẾT THÚC

4
2017-08-24 00:12



Điều này dường như trở lại thứ hai cho ngày hôm nay, không phải chủ nhật. OP đã có một chức năng mà trở lại thứ hai, ông muốn nó trở lại chủ nhật. :-) - Aaron Bertrand
doh! Tôi nên đọc câu hỏi cẩn thận hơn lần sau. Tuy nhiên, giải pháp của tôi có thể dễ dàng điều chỉnh, nếu vẫn yêu cầu. Có vẻ như OP vẫn hài lòng với câu trả lời được chấp nhận -) - trailmax
Đây là giải pháp đúng trên máy của tôi, vì đối với tôi: DATEADD (ww, DATEDIFF (ww, 0, CONVERT (DATE, '2017-10-8')), 0) trả về 2017-10-9! - Run CMD


Googled tập lệnh này:

create function dbo.F_START_OF_WEEK
(
    @DATE           datetime,
    -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
    -- Thu = 5, Fri = 6, Sat = 7
    -- Default to Sunday
    @WEEK_START_DAY     int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns     datetime
as
begin
declare  @START_OF_WEEK_DATE    datetime
declare  @FIRST_BOW     datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
    begin
    -- Find first day on or after 1753/1/1 (-53690)
    -- matching day of week of @WEEK_START_DAY
    -- 1753/1/1 is earliest possible SQL Server date.
    select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
    -- Verify beginning of week not before 1753/1/1
    if @DATE >= @FIRST_BOW
        begin
        select @START_OF_WEEK_DATE = 
        dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
        end
    end

return @START_OF_WEEK_DATE

end
go

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307


3
2017-08-23 23:54





Có lẽ bạn cần điều này:

SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

Hoặc là

DECLARE @MYDATE DATETIME
SET @MYDATE = '2011-08-23'
SELECT DATEADD(DD, 1 - DATEPART(DW, @MYDATE), @MYDATE)

Chức năng

CREATE FUNCTION [dbo].[GetFirstDayOfWeek]
( @pInputDate    DATETIME )
RETURNS DATETIME
BEGIN

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
RETURN DATEADD(DD, 1 - DATEPART(DW, @pInputDate),
               @pInputDate)

END
GO

2
2017-08-24 00:01



DATEPART(DW phụ thuộc vào @@datefirst - Martin Smith
@Martin Smith cũng có đầu óc! - Gandarez
Tôi thích sự đơn giản của cái này. Dường như nó chạy khá tốt cho các tập dữ liệu rất lớn. - Quick Joe Smith
Tại sao không chỉ tạo tham số đầu vào DATE thì bạn không phải thực hiện bất kỳ chuyển đổi phụ tối ưu nào VARCHAR và quay lại chỉ để loại bỏ bất kỳ thành phần thời gian ngẫu nhiên nào được truyền vào. - Aaron Bertrand
Hàm Chuyển đổi đã được sử dụng vì giá trị trả về không cần Time giá trị. - Gandarez


TẠO CHỨC NĂNG dbo.fnFirstWorkingDayOfTheWeek
(
    @currentDate date
)
RETURNS INT
NHƯ
BEGIN
    - nhận cài đặt DATEFIRST
    DECLARE @ds int = @@ DATEFIRST
    - nhận số ngày trong tuần theo cài đặt DATEFIRST hiện tại
    DECLARE @dow int = DATEPART (dw, @ currentDate)

    DECLARE @wd int = 1 + ((((@ dow + @ ds)% 7) +5)% 7 - điều này luôn trả về Thứ Hai là 1, Tuệ là 2 ... Mặt Trời là 7

    HOÀN TRẢ NGÀY DATE (đ, 1- @ wd, @ currentDate)

KẾT THÚC

2
2017-12-03 13:01



Đây là hàm duy nhất làm việc cho tôi trong SQL Server 2005. Cảm ơn bạn - Fandango68
@ Fernando68 Bạn có thể giải thích cách các giải pháp khác không công việc? - Aaron Bertrand
@AaronBertrand xin lỗi không nhớ lại, nhưng tôi nghĩ rằng tôi đã tập trung vào một câu trả lời nhanh chóng và tôi đã thử của bạn nhưng đối với một số lý do nó không làm việc cho tôi. - Fandango68
@ Fernando68 Vâng, đó là rất hữu ích. : - \ - Aaron Bertrand


Đối với những người cần câu trả lời tại nơi làm việc và chức năng tạo bị cấm bởi DBA của bạn, giải pháp sau sẽ hoạt động:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-1), YourDate) as DATE) as WeekStart
From.....

Điều này cho phép bắt đầu tuần đó. Ở đây tôi giả định rằng chủ nhật là sự khởi đầu của tuần. Nếu bạn nghĩ rằng thứ hai là bắt đầu, bạn nên sử dụng:

select *,
cast(DATEADD(day, -1*(DATEPART(WEEKDAY, YouDate)-2), YourDate) as DATE) as WeekStart
From.....

2
2018-03-30 16:56