DATETRUNC (Transact-SQL)

Artikel
11/15/2023

Berlaku untuk: Titik akhir analitik SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Fungsi mengembalikan DATETRUNC tanggal input yang dipotong ke datepart tertentu.

Sintaks

DATETRUNC ( datepart, date )

Argumen

datepart

Menentukan presisi untuk pemotongan. Tabel ini mencantumkan semua nilai datepart yang valid untuk DATETRUNC, mengingat bahwa tabel ini juga merupakan bagian yang valid dari jenis tanggal input.

datepart	Singkatan	Catatan pemotongan
Tahun	yy, yyyy
kuartal	qq, q
Bulan	mm, m
dayofyear	dy, y	dayofyear dipotong dengan cara yang sama seperti hari
hari	dd, d	hari dipotong dengan cara yang sama seperti dayofyear
minggu	wk, ww	Potong ke hari pertama dalam seminggu. Di T-SQL, hari pertama dalam seminggu didefinisikan oleh `@@DATEFIRST` pengaturan T-SQL. Untuk lingkungan Bahasa Inggris A.S. , `@@DATEFIRST` default ke 7 (Minggu).
iso_week	isowk, isoww	Potong ke hari pertama Minggu ISO. Hari pertama dalam seminggu dalam sistem kalender ISO8601 adalah Senin.
jam	hh
menit	mi, n
detik	ss, s
milidetik	ms
mikrosecond	Mcs

Catatan

Datepart T-SQL hari kerja, timezoneoffset, dan nanodetik tidak didukung untuk DATETRUNC.

date

Menerima variabel ekspresi, kolom, atau yang ditentukan pengguna apa pun yang dapat mengatasi jenis tanggal atau waktu T-SQL yang valid. Jenis yang valid adalah:

smalldatetime
datetime
date
time
datetime2
datetimeoffset

Jangan membingungkan parameter tanggal dengan jenis data tanggal .

DATETRUNC juga akan menerima string harfiah (dari jenis string apa pun) yang dapat diselesaikan ke datetime2(7).

Jenis Pengembalian

Jenis data yang dikembalikan untuk DATETRUNC bersifat dinamis. DATETRUNC mengembalikan tanggal terpotong dari jenis data yang sama (dan, jika berlaku, skala waktu pecahan yang sama) sebagai tanggal input. Misalnya, jika DATETRUNC diberi tanggal input datetimeoffset(3), tanggal input akan mengembalikan datetimeoffset(3). Jika diberi string harfiah yang dapat diselesaikan ke datetime2(7), DATETRUNC akan mengembalikan datetime2(7).

Presisi skala waktu pecahan

Milidetik memiliki skala waktu pecahan 3 (.123), mikrodetik memiliki skala waktu pecahan 6 (.123456), dan nanodetik memiliki skala waktu pecahan 9 (.123456789). Jenis data waktu, datetime2, dan datetimeoffset memungkinkan skala waktu pecahan maksimum 7 (.1234567). Oleh karena itu, untuk memotong ke millisecondbagian tanggal, skala waktu pecahan harus setidaknya 3. Demikian pula, untuk memotong ke microseconddatepart, skala waktu pecahan harus setidaknya 6. DATETRUNC tidak mendukung nanoseconddatepart karena tidak ada jenis tanggal T-SQL yang mendukung skala waktu pecahan 9.

Contoh

J. Gunakan opsi datepart yang berbeda

Contoh berikut mengilustrasikan penggunaan berbagai opsi datepart :

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

Berikut set hasilnya:

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

B. pengaturan @@DATEFIRST

Contoh berikut mengilustrasikan penggunaan @@DATEFIRST pengaturan dengan weekdatepart:

DECLARE @d datetime2 = '2021-11-11 11:11:11.1234567';

SELECT 'Week-7', DATETRUNC(week, @d); -- Uses the default DATEFIRST setting value of 7 (U.S. English)

SET DATEFIRST 6;
SELECT 'Week-6', DATETRUNC(week, @d);

SET DATEFIRST 3;
SELECT 'Week-3', DATETRUNC(week, @d);

Berikut set hasilnya:

Week-7  2021-11-07 00:00:00.0000000
Week-6  2021-11-06 00:00:00.0000000
Week-3  2021-11-10 00:00:00.0000000

C. Tanggal harfiah

Contoh berikut mengilustrasikan penggunaan literal parameter tanggal :

SELECT DATETRUNC(month, '1998-03-04');

SELECT DATETRUNC(millisecond, '1998-03-04 10:10:05.1234567');

DECLARE @d1 char(200) = '1998-03-04';
SELECT DATETRUNC(millisecond, @d1);

DECLARE @d2 nvarchar(max) = '1998-03-04 10:10:05';
SELECT DATETRUNC(minute, @d2);

Berikut adalah tataan hasilnya (semua hasilnya berjenis datetime2(7)):

1998-03-01 00:00:00.0000000
1998-03-04 10:10:05.1230000
1998-03-04 00:00:00.0000000
1998-03-04 10:10:00.0000000

D. Variabel dan parameter tanggal

Contoh berikut mengilustrasikan penggunaan parameter tanggal :

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

Berikut hasilnya:

1998-12-11 00:00:00.0000000

E. Kolom dan parameter tanggal

Kolom TransactionDate dari Sales.CustomerTransactions tabel berfungsi sebagai contoh argumen kolom untuk parameter tanggal :

USE WideWorldImporters;

SELECT CustomerTransactionID,
    DATETRUNC(month, TransactionDate) AS MonthTransactionOccurred,
    InvoiceID,
    CustomerID,
    TransactionAmount,
    SUM(TransactionAmount) OVER (
        PARTITION BY CustomerID ORDER BY TransactionDate,
            CustomerTransactionID ROWS UNBOUNDED PRECEDING
        ) AS RunningTotal,
    TransactionDate AS ActualTransactionDate
FROM [WideWorldImporters].[Sales].[CustomerTransactions]
WHERE InvoiceID IS NOT NULL
    AND DATETRUNC(month, TransactionDate) >= '2015-12-01';

F. Ekspresi dan parameter tanggal

Parameter tanggal menerima ekspresi apa pun yang dapat diselesaikan ke jenis tanggal T-SQL atau string literal apa pun yang dapat diselesaikan ke datetime2(7). Kolom TransactionDate dari Sales.CustomerTransactions tabel berfungsi sebagai argumen buatan untuk mencontohkan penggunaan ekspresi untuk parameter tanggal :

SELECT DATETRUNC(m, SYSDATETIME());

SELECT DATETRUNC(yyyy, CONVERT(date, '2021-12-1'));

USE WideWorldImporters;
GO
SELECT DATETRUNC(month, DATEADD(month, 4, TransactionDate))
FROM Sales.CustomerTransactions;
GO

G. Memotong tanggal ke bagian tanggal yang menunjukkan presisi maksimumnya

Jika datepart memiliki presisi maksimum unit yang sama dengan jenis tanggal input, memotong tanggal input ke datepart ini tidak akan berpengaruh.

Contoh 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Berikut adalah tataan hasil, yang menggambarkan bahwa tanggalwaktu input dan parameter tanggal terpotong sama:

Input     2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123

Contoh 2

DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

Berikut adalah tataan hasil, yang menggambarkan bahwa tanggalwaktu input dan parameter tanggal terpotong sama:

Input     2050-04-04
Truncated 2050-04-04

Contoh 3: presisi smalldatetime

smalldatetime hanya tepat hingga menit terdekat, meskipun memiliki bidang selama beberapa detik. Oleh karena itu, memotongnya ke menit terdekat atau detik terdekat tidak akan berpengaruh.

DECLARE @d smalldatetime = '2009-09-11 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);

Kumpulan hasil mengilustrasikan bahwa nilai smalldatetime input sama dengan kedua nilai yang dipotong:

Input                2009-09-11 12:42:00
Truncated to minute  2009-09-11 12:42:00
Truncated to second  2009-09-11 12:42:00

Contoh 4: presisi tanggalwaktu

datetime hanya tepat hingga 3,33 milidetik. Oleh karena itu, memotong tanggalwaktu hingga milidetik dapat menghasilkan hasil yang berbeda dari yang diharapkan pengguna. Namun, nilai terpotong ini sama dengan nilai tanggalwaktu yang disimpan secara internal.

DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Berikut adalah kumpulan hasil, yang menggambarkan bahwa tanggal terpotong sama dengan tanggal tersimpan. Ini mungkin berbeda dengan yang Anda harapkan berdasarkan pernyataan.DECLARE

Input     2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003

Keterangan

Kesalahan DATE TOO SMALL dilemparkan jika pemotongan tanggal mencoba untuk melakukan backtrack ke tanggal sebelum tanggal minimum yang didukung oleh jenis data tersebut. Ini hanya terjadi saat menggunakan datepartweek. Ini tidak dapat terjadi saat menggunakan iso_weekdatepart, karena semua jenis tanggal T-SQL secara kebetulan menggunakan Hari Senin untuk tanggal minimumnya. Berikut adalah contoh dengan pesan kesalahan hasil yang sesuai:

DECLARE @d date= '0001-01-01 00:00:00';
SELECT DATETRUNC(week, @d);

Msg 9837, Level 16, State 3, Line 84
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.

Kesalahan DATEPART dilemparkan jika datepart yang digunakan tidak didukung oleh DATETRUNC fungsi atau jenis data tanggal input. Ini dapat terjadi ketika:

Datepart yang tidak didukung oleh DATETRUNC digunakan (yaitu, , weekdaytzoffset, atau nanosecond)

Datepart terkait waktu digunakan dengan jenis data tanggal atau tanggal terkait tanggal digunakan dengan jenis data waktu. Berikut adalah contoh dengan pesan kesalahan hasil yang sesuai:

DECLARE @d time = '12:12:12.1234567';
SELECT DATETRUNC(year, @d);

Msg 9810, Level 16, State 10, Line 78
The datepart year is not supported by date function datetrunc for data type time.

Datepart memerlukan presisi skala waktu pecahan yang lebih tinggi daripada yang didukung oleh jenis data (Lihat Presisi skala waktu pecahan). Berikut adalah contoh dengan pesan kesalahan hasil yang sesuai:

DECLARE @d datetime2(3) = '2021-12-12 12:12:12.12345';
SELECT DATETRUNC(microsecond, @d);

Msg 9810, Level 16, State 11, Line 81
The datepart microsecond is not supported by date function datetrunc for data type datetime2.