Pertanyaan Dapatkan hari pertama dalam seminggu di SQL Server


Saya mencoba mengelompokkan catatan menurut minggu, menyimpan tanggal keseluruhan sebagai hari pertama dalam seminggu. Namun, teknik standar yang saya gunakan untuk membulatkan tanggal tampaknya tidak bekerja dengan benar selama berminggu-minggu (meskipun itu berlaku untuk hari, bulan, tahun, kuartal dan jangka waktu lain yang telah saya terapkan).

Berikut ini adalah SQL:

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

Ini kembali 2011-08-22 00:00:00.000, yaitu hari Senin, bukan hari Minggu. Memilih @@datefirst kembali 7, yang merupakan kode untuk hari Minggu, jadi server disiapkan dengan benar sejauh yang saya tahu.

Saya dapat melewati ini cukup mudah dengan mengubah kode di atas ke:

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

Tetapi fakta bahwa saya harus membuat pengecualian semacam itu membuat saya sedikit gelisah. Juga, mohon maaf jika ini adalah pertanyaan duplikat. Saya menemukan beberapa pertanyaan terkait tetapi tidak ada yang membahas aspek ini secara khusus.


76
2017-08-23 23:50


asal


Jawaban:


Untuk menjawab mengapa Anda mendapatkan hari Senin dan bukan hari Minggu:

Anda menambahkan beberapa minggu ke tanggal 0. Apa itu tanggal 0? 1900-01-01. Apa hari di 1900-01-01? Senin. Jadi dalam kode Anda, Anda mengatakan, berapa minggu telah berlalu sejak Senin, 1 Januari 1900? Mari kita sebut itu [n]. Ok, sekarang tambahkan [n] minggu ke Senin, 1 Januari 1900. Anda tidak perlu terkejut bahwa ini berakhir menjadi hari Senin. DATEADD tidak tahu bahwa Anda ingin menambahkan minggu tetapi hanya sampai Anda mencapai hari Minggu, itu hanya menambahkan 7 hari, kemudian menambahkan 7 hari lagi, ... seperti DATEDIFF hanya mengenali batas yang telah dilintasi. Sebagai contoh, keduanya kembali 1, meskipun beberapa orang mengeluh bahwa harus ada logika yang masuk akal untuk membulatkan ke atas atau ke bawah:

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

Untuk menjawab bagaimana mendapatkan hari Minggu:

Jika Anda menginginkan hari Minggu, maka pilih tanggal dasar yang bukan hari Senin melainkan hari Minggu. Sebagai contoh:

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

Ini tidak akan pecah jika Anda mengubah Anda DATEFIRST pengaturan (atau kode Anda berjalan untuk pengguna dengan pengaturan yang berbeda) - asalkan Anda masih menginginkan hari Minggu terlepas dari pengaturan saat ini. Jika Anda menginginkan kedua jawaban itu, maka Anda harus menggunakan fungsi itu tidak tergantung pada DATEFIRST pengaturan, misalnya

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

Jadi, jika Anda mengubah Anda DATEFIRST pengaturan untuk hari Senin, Selasa, apa yang Anda lakukan, perilaku akan berubah. Tergantung pada perilaku yang Anda inginkan, Anda dapat menggunakan salah satu dari fungsi-fungsi ini:

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

...atau...

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

Sekarang, Anda memiliki banyak alternatif, tetapi yang mana yang berkinerja terbaik? Saya akan terkejut jika akan ada perbedaan besar tetapi saya mengumpulkan semua jawaban yang diberikan sejauh ini dan menjalankannya melalui dua rangkaian tes - yang murah dan mahal. Saya mengukur statistik klien karena saya tidak melihat I / O atau memori memainkan bagian dalam kinerja di sini (meskipun itu mungkin ikut bermain tergantung pada bagaimana fungsi tersebut digunakan). Dalam pengujian saya hasilnya adalah:

Kueri tugas "Murah":

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

Kueri tugas "Mahal":

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

Saya dapat menyampaikan rincian tes saya jika diinginkan - berhenti di sini karena ini sudah cukup bertele-tele. Saya sedikit terkejut melihat Curt's keluar sebagai yang tercepat di high end, mengingat jumlah perhitungan dan kode inline. Mungkin saya akan menjalankan beberapa tes lebih menyeluruh dan blog tentang itu ... jika kalian tidak keberatan dengan saya mempublikasikan fungsi Anda di tempat lain.


119
2017-08-24 02:01



Untuk ini yang perlu mendapatkan:

Senin = 1 dan Minggu = 7:

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

Minggu = 1 dan Sabtu = 7:

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

Di atas ada contoh serupa, tetapi berkat dua kali lipat "% 7" itu akan jauh lebih lambat.


11
2018-02-14 16:21



Ini sangat bermanfaat bagi saya:

BUAT FUNGSI [dbo]. [StartOfWeek]
(
  @INPUTDATE DATETIME
)
KEMBALIKAN DATETIME

SEBAGAI
MULAI
  - INI tidak berfungsi.
  - SET DATEFIRST 1 - set Senin untuk menjadi hari pertama dalam seminggu.

  DECLARE @ DOW INT - untuk menyimpan hari dalam seminggu
  SET @INPUTDATE = CONVERT (VARCHAR (10), @INPUTDATE, 111)
  SET @ DOW = DATEPART (DW, @INPUTDATE)

  - Sihir konversi hari Senin ke 1, selasa ke 2, dll.
  - Mengabaikan apa yang dipikirkan server SQL tentang awal pekan.
  - Tapi di sini kita memiliki minggu yang ditandai sebagai 0, tetapi kita memperbaikinya nanti.
  SET @ DOW = (@DOW + @@ DATEFIRST - 1)% 7
  IF @ DOW = 0 SET @ DOW = 7 - perbaiki untuk minggu

  KEMBALIKAN DATEADD (DD, 1 - @ DOW, @ INPUTDATE)

AKHIR

4
2017-08-24 00:12



Googled script ini:

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



Mungkin Anda membutuhkan ini:

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

Atau

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

Fungsi

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



BUAT FUNGSI dbo.fnFirstWorkingDayOfTheWeek
(
    tanggal @currentDate
)
KEMBALIKAN INT
SEBAGAI
MULAI
    - dapatkan pengaturan DATEFIRST
    DECLARE @ds int = @@ DATEFIRST
    - dapatkan nomor hari minggu di bawah pengaturan DATEFIRST saat ini
    DECLARE @dow int = DATEPART (dw, @ currentDate)

    DECLARE @wd int = 1 + ((@ dow + @ ds)% 7) +5)% 7 - ini selalu mengembalikan Mon sebagai 1, Selesai sebagai 2 ... Sun as 7

    KEMBALIKAN DATEADD (dd, 1- @ wd, @ currentDate)

AKHIR

2
2017-12-03 13:01



Bagi mereka yang membutuhkan jawaban di tempat kerja dan membuat fungsi dilarang oleh DBA Anda, solusi berikut akan berfungsi:

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

Ini memberikan permulaan minggu itu. Di sini saya berasumsi bahwa hari Minggu adalah permulaan minggu. Jika Anda berpikir bahwa Senin adalah awal, Anda harus menggunakan:

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

2
2018-03-30 16:56



Karena Julian tanggal 0 adalah hari Senin, tambahkan saja jumlah minggu ke hari Minggu yaitu sehari sebelum -1 Eg. pilih dateadd (wk, datediff (wk, 0, getdate ()), - 1)


0
2017-09-24 06:46



Set DateFirst 1;

Select 
    Datepart(wk, TimeByDay) [Week]
    ,Dateadd(d,
                CASE 
                WHEN  Datepart(dw, TimeByDay) = 1 then 0
                WHEN  Datepart(dw, TimeByDay) = 2 then -1
                WHEN  Datepart(dw, TimeByDay) = 3 then -2
                WHEN  Datepart(dw, TimeByDay) = 4 then -3
                WHEN  Datepart(dw, TimeByDay) = 5 then -4
                WHEN  Datepart(dw, TimeByDay) = 6 then -5
                WHEN  Datepart(dw, TimeByDay) = 7 then -6
                END
                , TimeByDay) as StartOfWeek

from TimeByDay_Tbl

Ini adalah logika saya. Tetapkan minggu pertama untuk hari Senin lalu hitung hari hari apa hari memberi, lalu gunakan DateAdd dan Case I hitung tanggal apa yang akan terjadi pada hari Senin sebelumnya pada minggu itu.


0
2018-05-21 04:07