Bagikan melalui

DATEDIFF (T-SQL)

  • Artikel

Berlaku untuk: SQL Server 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 untuk fungsi yang menangani perbedaan yang lebih besar antara nilai startdate dan enddate . Lihat Jenis dan fungsi data tanggal dan waktu untuk gambaran umum semua jenis dan fungsi data tanggal dan waktu Transact-SQL.

Konvensi sintaks transact-SQL

Sintaks

DATEDIFF ( datepart , startdate , enddate )

Argumen

datepart

Menentukan 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 dari datepart, atau singkatan dari nama lengkap yang tercantum.

nama datepart singkatan datepart
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

Setiap nama dan singkatan datepart tertentu untuk nama datepart tersebut 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 Konfigurasi server: cutoff dua digit tahun untuk informasi tentang nilai tahun dua digit.

enddate

Lihat mulai.

Jenis yang dikembalikan

int

Nilai hasil

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 kamp. Kasus ini berarti bahwa jika kita mulai dari awal 2036-03-01 , dan kemudian menghitung -2 hari, kita mencapai akhir dari 2036-02-28.

Untuk nilai pengembalian di luar rentang untuk int (-2.147.483.648 hingga +2.147.483.647), DATEDIFF mengembalikan kesalahan. Untuk millisecond, perbedaan maksimum antara tanggal mulai dan berakhir adalah 24 hari, 20 jam, 31 menit, dan 23,647 detik. Untuk second, 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 mengembalikan 0 untuk datepart week.

Keterangan

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

DATEDIFF secara implisit melemparkan literal string sebagai jenis datetime2 . Dengan kata lain, DATEDIFF tidak mendukung format YDM saat tanggal diteruskan sebagai string. Anda harus secara eksplisit mentransmisikan string ke jenis datetime atau smalldatetime untuk menggunakan YDM format .

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 minute atau lebih tinggi, jika perbedaan antara tanggal berakhir dan tanggal mulai mengembalikan nilai yang berada di luar rentang untuk int.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

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

J. Tentukan 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;

Berikut set hasilnya.

Duration
--------
1

B. Tentukan variabel yang ditentukan pengguna untuk startdate dan enddate

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

DECLARE @startdate AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';

SELECT DATEDIFF(day, @startdate, @enddate);

C. Tentukan 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. Tentukan 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. Tentukan 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. Tentukan 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. Tentukan 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 AS s
     INNER JOIN Person.Person AS p
         ON s.BusinessEntityID = p.BusinessEntityID
     INNER JOIN Person.Address AS a
         ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
      AND SalesYTD <> 0;

H. Tentukan fungsi jendela agregat untuk memulai

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 AS sod
     INNER JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO

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

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);

DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS 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 set hasilnya.

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

Duration
--------
1

K. Tentukan subkueri skalar dan fungsi skalar untuk startdate dan enddate

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. Tentukan konstanta untuk tanggal mulai dan berakhir

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

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

M. Tentukan fungsi peringkat untuk startdate

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. Tentukan fungsi jendela agregat untuk memulai

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;

Konten terkait