DATEDIFF_BIG (Transact-SQL)

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance

Fungsi ini mengembalikan hitungan (sebagai nilai bilangan bulat besar yang ditandatangani) dari batas datepart yang ditentukan yang disilangkan antara tanggal mulai dan berakhir yang ditentukan.

Lihat Jenis dan Fungsi Data Tanggal dan Waktu (Transact-SQL) untuk gambaran umum semua jenis dan fungsi data tanggal dan waktu Transact-SQL.

Ikon tautan topikKonvensi Sintaks Transact-SQL

Sintaks

DATEDIFF_BIG ( datepart , startdate , enddate )  

Argumen

datepart
Bagian dari tanggal mulai dan berakhir yang menentukan jenis batas yang disilangkan.

Catatan

DATEDIFF_BIG tidak akan menerima nilai datepart dari variabel yang ditentukan pengguna atau sebagai string yang dikutip.

Tabel ini mencantumkan semua nama argumen dan singkatan datepart yang valid.

nama datepart singkatan datepart
tahun yy, yyyy
kuartal qq, q
bulan mm, m
dayofyear dy, y
hari dd, d
minggu wk, ww
jam hh
menit mi, n
detik ss, s
milidetik ms
mikrosecond Mcs
nanodetik Ns

Catatan

Setiap nama dan singkatan datepart tertentu untuk nama datepart tersebut akan mengembalikan nilai yang sama.

startdate
Ekspresi yang bisa diatasi ke salah satu nilai berikut ini:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

Untuk tanggal, DATEDIFF_BIG akan menerima ekspresi kolom, ekspresi, string literal, atau variabel yang ditentukan pengguna. Nilai literal string harus diselesaikan ke tanggalwaktu. Gunakan empat digit tahun untuk menghindari masalah ambiguitas. DATEDIFF_BIG mengurangi dimulai dari tanggal berakhir. Untuk menghindari ambiguitas, gunakan empat digit tahun. Lihat Mengonfigurasi Opsi Konfigurasi Server cutoff dua digit tahun untuk informasi tentang dua digit tahun.

enddate
Lihat mulai.

Jenis Hasil

Ditandatangani bigint

Tampilkan Nilai

Mengembalikan perbedaan bigint antara tanggal mulai dan berakhir, yang dinyatakan dalam batas yang ditetapkan oleh datepart.

Untuk nilai pengembalian di luar rentang untuk bigint (-9.223.372.036.854.775.808 hingga 9.223.372.036.854.775.807), DATEDIFF_BIG mengembalikan kesalahan. Tidak seperti , yang mengembalikan int dan oleh karena itu dapat meluap satu menit atau lebih tinggi, DATEDIFF_BIG hanya dapat meluap jika menggunakan presisi nanodetik di mana perbedaan antara enddate dan startdate lebih dari 292 tahun, 3 bulan, 10 hari, 23 jam, 47 menit, dan 16,8547758 detik.

Jika tanggal mulai dan berakhir keduanya hanya ditetapkan nilai waktu, dan datepart bukan tanggalwaktu, DATEDIFF_BIG mengembalikan 0.

DATEDIFF_BIG menggunakan komponen offset zona waktu dari tanggal mulai atau berakhir untuk menghitung nilai yang dikembalikan.

Untuk nilai smalldatetime yang digunakan untuk startdate atau enddate, DATEDIFF_BIG selalu atur detik dan milidetik ke 0 dalam nilai pengembalian karena smalldatetime hanya memiliki akurasi hingga menit.

Jika hanya nilai waktu yang ditetapkan ke variabel jenis data tanggal, DATEDIFF_BIG atur nilai bagian tanggal yang hilang ke nilai default: 1900-01-01. Jika hanya nilai tanggal yang ditetapkan ke variabel jenis data waktu atau tanggal, DATEDIFF_BIG mengatur nilai bagian waktu yang hilang ke nilai default: 00:00:00. Jika tanggal mulai atau berakhir hanya memiliki bagian waktu dan yang lain hanya bagian tanggal, DATEDIFF_BIG mengatur bagian waktu dan tanggal yang hilang ke nilai default.

Jika startdate dan enddate memiliki jenis data tanggal yang berbeda, dan satu memiliki lebih banyak bagian waktu atau presisi detik pecahan daripada yang lain, DATEDIFF_BIG mengatur bagian yang hilang dari yang lain ke 0.

batas datepart

Pernyataan berikut memiliki nilai startdate dan enddate yang sama. Tanggal-tanggal tersebut berdekatan dan mereka berbeda dalam waktu dengan seratus nanodetik (.0000001 detik). Perbedaan antara tanggal mulai dan berakhir di setiap pernyataan melewati satu kalender atau batas waktu tanggalnya. Setiap pernyataan mengembalikan 1. Jika tanggal mulai dan berakhir memiliki nilai tahun yang berbeda tetapi memiliki nilai minggu kalender yang sama, DATEDIFF_BIG akan mengembalikan 0 untuk minggudatepart.

SELECT DATEDIFF_BIG(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Keterangan

Gunakan DATEDIFF_BIG dalam SELECT <list>klausa , WHERE, HAVING, GROUP BY dan ORDER BY .

DATEDIFF_BIG secara implisit melemparkan literal string sebagai jenis datetime2 . Ini berarti bahwa DATEDIFF_BIG tidak mendukung format YDM saat tanggal diteruskan sebagai string. Anda harus secara eksplisit mentransmisikan string ke jenis datetime atau smalldatetime untuk menggunakan format YDM.

Menentukan SET DATEFIRST tidak berpengaruh pada DATEDIFF_BIG. DATEDIFF_BIG selalu menggunakan Hari Minggu sebagai hari pertama dalam seminggu untuk memastikan fungsi beroperasi dengan cara deterministik.

DATEDIFF_BIG dapat meluap dengan nanodetik jika perbedaan antara enddate dan startdate mengembalikan nilai yang berada di luar rentang untuk bigint.

Contoh

Menentukan kolom untuk tanggal mulai dan berakhir

Contoh ini menggunakan berbagai jenis ekspresi sebagai argumen untuk parameter startdate dan enddate . Ini menghitung jumlah batas hari yang disilangkan di antara tanggal dalam dua kolom tabel.

CREATE TABLE dbo.Duration  
    (startDate datetime2, endDate datetime2);  
    
INSERT INTO dbo.Duration(startDate,endDate)  
    VALUES('2007-05-06 12:10:09', '2007-05-07 12:10:09');  
    
SELECT DATEDIFF_BIG(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1  

Menemukan perbedaan antara tanggal mulai dan berakhir sebagai string bagian tanggal

DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT

SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1 
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1 
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1 
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1 
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1 
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1 
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
          + CASE WHEN @milliseconds > 0 THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
               ELSE '' END 
          + ' seconds','')

SELECT @result

Berikut adalah hasil yang ditetapkan.

2017 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

Lihat contoh yang lebih terkait erat di DATEDIFF (Transact-SQL).

Lihat juga

CAST dan CONVERT (Transact-SQL)
DATEDIFF (Transact-SQL)