DATEDIFF (T-SQL)

Artikel
10/20/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics Analytics Platform System (PDW)

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

Lihat DATEDIFF_BIG (Transact-SQL) untuk fungsi yang menangani perbedaan yang lebih besar antara nilai startdate dan enddate . Lihat Jenis dan Fungsi Data Tanggal dan Waktu (Transact-SQL) untuk gambaran umum semua jenis dan fungsi data tanggal dan waktu Transact-SQL.

Konvensi sintaks transact-SQL

Sintaksis

DATEDIFF ( datepart , startdate , enddate )

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

datepart
Unit di mana DATEDIFF melaporkan perbedaan antara tanggal mulai dan berakhir. Unit datepart yang umum digunakan meliputi month atau second.

Nilai datepart tidak dapat ditentukan dalam variabel, atau sebagai string yang dikutip seperti 'month'.

Tabel berikut mencantumkan semua nilai datepart yang valid. DATEDIFF menerima nama lengkap datepart, atau singkatan dari nama lengkap yang tercantum.

nama datepart	singkatan datepart
Tahun	y, yy, yyy
kuartal	qq, q
Bulan	mm, m
dayofyear	Dy
hari	dd, d
minggu	wk, ww
hari kerja	dw, w
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

Gunakan empat digit tahun untuk menghindari ambiguitas. Lihat Mengonfigurasi Opsi Konfigurasi Server cutoff dua digit tahun untuk informasi tentang nilai tahun dua digit.

enddate
Lihat mulai.

Jenis Hasil

int

Tampilkan Nilai

Perbedaan int antara tanggal mulai dan berakhir, dinyatakan dalam batas yang ditetapkan oleh datepart.

Misalnya, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); mengembalikan -2, mengisyaratkan bahwa 2036 harus berupa tahun kampung. Kasus ini berarti bahwa jika kita mulai pada awal '2036-03-01', dan kemudian menghitung -2 hari, kita mencapai akhir '2036-02-28'.

Untuk nilai pengembalian di luar rentang untuk int (-2.147.483.648 hingga +2.147.483.647), DATEDIFF mengembalikan kesalahan. Untuk milidetik, perbedaan maksimum antara tanggal mulai dan berakhir adalah 24 hari, 20 jam, 31 menit dan 23,647 detik. Untuk kedua, perbedaan maksimum adalah 68 tahun, 19 hari, 3 jam, 14 menit dan 7 detik.

Jika startdate dan enddate keduanya hanya ditetapkan nilai waktu, dan datepart bukan time datepart, DATEDIFF mengembalikan 0.

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

Karena smalldatetime hanya akurat hingga menit, detik dan milidetik selalu diatur ke 0 dalam nilai pengembalian saat tanggal mulai atau berakhir memiliki nilai smalldatetime .

Jika hanya nilai waktu yang ditetapkan ke variabel jenis data tanggal, DATEDIFF 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 atur nilai bagian waktu yang hilang ke nilai default: 00:00:00. Jika startdate atau enddate hanya memiliki bagian waktu dan yang lainnya hanya bagian tanggal, DATEDIFF 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 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 berbeda pada waktunya dengan seratus nanodetik (.0000001 detik). Perbedaan antara tanggal mulai dan berakhir di setiap pernyataan melewati satu kalender atau batas waktu dari datepart-nya. Setiap pernyataan mengembalikan 1.

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

Jika tanggal mulai dan berakhir memiliki nilai tahun yang berbeda, tetapi memiliki nilai minggu kalender yang sama, DATEDIFF akan mengembalikan 0 untuk minggu datepart.

Keterangan

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

DATEDIFF secara implisit melemparkan literal string sebagai jenis datetime2 . Ini berarti bahwa DATEDIFF tidak mendukung format YDM ketika 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. DATEDIFF selalu menggunakan Hari Minggu sebagai hari pertama dalam seminggu untuk memastikan fungsi beroperasi dengan cara deterministik.

DATEDIFF mungkin meluap dengan presisi menit atau lebih tinggi jika perbedaan antara tanggal berakhir dan tanggal mulai mengembalikan nilai yang berada di luar rentang untuk int.

Contoh

Contoh-contoh ini menggunakan berbagai jenis ekspresi sebagai argumen untuk parameter startdate dan enddate .

J. Menentukan kolom untuk tanggal mulai dan berakhir

Contoh ini menghitung jumlah batas hari yang disilangkan di antara tanggal dalam dua kolom dalam 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(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1

B. Menentukan variabel yang ditentukan pengguna untuk tanggal mulai dan berakhir

Dalam contoh ini, variabel yang ditentukan pengguna berfungsi sebagai argumen untuk tanggal mulai dan berakhir.

DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722';  
DECLARE @enddate   DATETIME2 = '2007-05-04 12:10:09.3312722';   
SELECT DATEDIFF(day, @startdate, @enddate);

C. Menentukan fungsi sistem skalar untuk startdate dan enddate

Contoh ini menggunakan fungsi sistem skalar sebagai argumen untuk startdate dan enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

D. Menentukan subkueri skalar dan fungsi skalar untuk startdate dan enddate

Contoh ini menggunakan subkueri skalar dan fungsi skalar sebagai argumen untuk startdate dan enddate.

USE AdventureWorks2022;  
GO  
SELECT DATEDIFF(day,
    (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),  
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

E. Menentukan konstanta untuk tanggal mulai dan berakhir

Contoh ini menggunakan konstanta karakter sebagai argumen untuk tanggal mulai dan berakhir.

SELECT DATEDIFF(day,
   '2007-05-07 09:53:01.0376635',
   '2007-05-08 09:53:01.0376635');

F. Menentukan ekspresi numerik dan fungsi sistem skalar untuk enddate

Contoh ini menggunakan ekspresi numerik, (GETDATE() + 1), dan fungsi GETDATE sistem skalar dan SYSDATETIME, sebagai argumen untuk enddate.

USE AdventureWorks2022;  
GO  
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1)
    AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  
USE AdventureWorks2022;  
GO  
SELECT
    DATEDIFF(
            day,
            '2007-05-07 09:53:01.0376635',
            DATEADD(day, 1, SYSDATETIME())
        ) AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO

G. Menentukan fungsi peringkat untuk startdate

Contoh ini menggunakan fungsi peringkat sebagai argumen untuk startdate.

USE AdventureWorks2022;  
GO  
SELECT p.FirstName, p.LastName  
    ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        a.PostalCode), SYSDATETIME()) AS 'Row Number'  
FROM Sales.SalesPerson s   
    INNER JOIN Person.Person p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;

H. Menentukan fungsi jendela agregat untuk tanggal mulai

Contoh ini menggunakan fungsi jendela agregat sebagai argumen untuk memulai.

USE AdventureWorks2022;  
GO  
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate,
    DATEDIFF(day, MIN(soh.OrderDate)   
        OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total'  
FROM Sales.SalesOrderDetail sod  
    INNER JOIN Sales.SalesOrderHeader soh  
        ON sod.SalesOrderID = soh.SalesOrderID  
WHERE soh.SalesOrderID IN(43659, 58918);  
GO

I. Menemukan perbedaan antara tanggal mulai dan berakhir sebagai string bagian tanggal

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
    @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'

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.

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

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)