DATEDIFF (SQL Transaksi)

Artikel
07/20/2022
8 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure 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 (SQL Transact) untuk gambaran umum semua jenis dan fungsi data tanggal dan waktu transact-SQL.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

DATEDIFF ( datepart , startdate , enddate )

Catatan

Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan 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 ini 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, yyyy
kuartal	qq, q
bulan	mm, m
dayofyear	Dy
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

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 merupakan tahun kabut. 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, selisih 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 untuk 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 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 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 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.

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(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 minggudatepart.

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 dapat 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 startdate dan enddate

Dalam contoh ini, variabel yang ditentukan pengguna berfungsi sebagai argumen untuk startdate dan enddate.

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 memulai dan mengakhiri

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

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

E. Menentukan konstanta untuk startdate dan enddate

Contoh ini menggunakan konstanta karakter sebagai argumen untuk startdate dan enddate.

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 berakhir.

USE AdventureWorks2012;  
GO  
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1)
    AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  
USE AdventureWorks2012;  
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 memulai

Contoh ini menggunakan fungsi peringkat sebagai argumen untuk startdate.

USE AdventureWorks2012;  
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 startdate

Contoh ini menggunakan fungsi jendela agregat sebagai argumen untuk memulai.

USE AdventureWorks2012;  
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)

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

j. Menentukan kolom untuk startdate dan enddate

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 TOP(1) DATEDIFF(day, startDate, endDate) AS Duration  
    FROM dbo.Duration;  
-- Returns: 1

K. Menentukan subkueri skalar dan fungsi skalar untuk memulai dan mengakhiri

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

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee),  
    (SELECT MAX(HireDate) FROM dbo.DimEmployee))   
FROM dbo.DimEmployee;

L. Menentukan konstanta untuk startdate dan enddate

Contoh ini menggunakan konstanta karakter sebagai argumen untuk startdate dan enddate.

-- Uses AdventureWorks  
  
SELECT TOP(1) DATEDIFF(day,
    '2007-05-07 09:53:01.0376635',
    '2007-05-08 09:53:01.0376635') FROM DimCustomer;

M. Menentukan fungsi peringkat untuk memulai

Contoh ini menggunakan fungsi peringkat sebagai argumen untuk startdate.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName,
    DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        DepartmentName), SYSDATETIME()) AS RowNumber  
FROM dbo.DimEmployee;

N. Menentukan fungsi jendela agregat untuk startdate

Contoh ini menggunakan fungsi jendela agregat sebagai argumen untuk memulai.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, DepartmentName,
    DATEDIFF(year, MAX(HireDate)  
        OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue  
FROM dbo.DimEmployee

Lihat juga

DATEDIFF_BIG (SQL bertransaksi)
CAST dan CONVERT (SQL Transact)