CAST dan CONVERT (Transact-SQL)
Berlaku untuk:
Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric
Fungsi-fungsi ini mengonversi ekspresi satu jenis data ke jenis data lainnya.
Sintaks
CAST sintaksis:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT sintaksis:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Argumen
expression
Ekspresi apa pun yang valid.
data_type
Jenis data target. Ini termasuk xml, bigint, dan sql_variant. Jenis data alias tidak dapat digunakan.
length
Bilangan bulat opsional yang menentukan panjang jenis data target, untuk jenis data yang memungkinkan panjang yang ditentukan pengguna. Nilai default adalah 30.
gaya
Ekspresi bilangan bulat yang menentukan bagaimana CONVERT fungsi akan menerjemahkan ekspresi. Untuk nilai gaya NULL, NULL dikembalikan. data_type menentukan rentang.
Jenis yang dikembalikan
Mengembalikan ekspresi, diterjemahkan ke data_type.
Gaya tanggal dan waktu
Untuk ekspresi tipe data tanggal atau waktu, gaya bisa memiliki salah satu nilai yang diperlihatkan dalam tabel berikut ini. Nilai lain diproses sebagai 0. Dimulai dengan SQL Server 2012 (11.x), satu-satunya gaya yang didukung, saat mengonversi dari jenis tanggal dan waktu ke datetimeoffset, adalah 0 atau 1. Semua gaya konversi lainnya mengembalikan kesalahan 9809.
Catatan
SQL Server mendukung format tanggal, dalam gaya Arab, dengan algoritma Kuwaiti.
| Tanpa abad (yy) 1 | Dengan abad (yyyy) | Standard | Input/output 3 |
|---|---|---|---|
| - | 0 atau 100 1, 2 | Default untuk datetime dan smalldatetime | mon dd yyyy hh:miAM (atau PM) |
| 1 | 101 | A.S. | 1 = mm/dd/yy101 = mm/dd/yyyy |
| 2 | 102 | ANSI | 2 = yy.mm.dd102 = yyyy.mm.dd |
| 3 | 103 | Inggris/Prancis | 3 = dd/mm/yy103 = dd/mm/yyyy |
| 4 | 104 | Jerman | 4 = dd.mm.yy104 = dd.mm.yyyy |
| 5 | 105 | Italia | 5 = dd-mm-yy105 = dd-mm-yyyy |
| 6 | 106 1 | - | 6 = dd mon yy106 = dd mon yyyy |
| 7 | 107 1 | - | 7 = Mon dd, yy107 = Mon dd, yyyy |
| 8 atau 24 | 108 | - | hh:mi:ss |
| - | 9 atau 109 1, 2 | Default + milidetik | mon dd yyyy hh:mi:ss:mmmAM (atau PM) |
| 10 | 110 | AS | 10 = mm-dd-yy 110 = mm-dd-yyyy |
| 11 | 111 | JEPANG | 11 = yy/mm/dd 111 = yyyy/mm/dd |
| 12 | 112 | ISO | 12 = yymmdd 112 = yyyymmdd |
| - | 13 atau 113 1, 2 | Default Eropa + milidetik | dd mon yyyy hh:mi:ss:mmm (24 jam) |
| 14 | 114 | - | hh:mi:ss:mmm (24 jam) |
| - | 20 atau 120 2 | Kanonis ODBC | yyyy-mm-dd hh:mi:ss (24 jam) |
| - | 21 atau 25 atau 121 2 | Default kanonis ODBC (dengan milidetik) untuk waktu, tanggal, tanggalwaktu2, dan datetimeoffset | yyyy-mm-dd hh:mi:ss.mmm (24 jam) |
| 22 | - | A.S. | mm/dd/yy hh:mi:ss AM (atau PM) |
| - | 23 | ISO8601 | yyyy-mm-dd |
| - | 126 4 | ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (tanpa spasi) 6 |
| - | 127 8, 9 | ISO8601 dengan zona waktu Z | yyyy-MM-ddThh:mm:ss.fffZ (tanpa spasi) 6 |
| - | 130 1, 2 | Hijriah 5 | dd mon yyyy hh:mi:ss:mmmAM7 |
| - | 131 2 | Hijriah 5 | dd/mm/yyyy hh:mi:ss:mmmAM |
1 Nilai gaya ini mengembalikan hasil nondeterministik. Mencakup semua gaya (yy) (tanpa abad) dan subset gaya (yyyy) (dengan abad).
2 Nilai default (0 atau 100, 9 atau 109, 13 atau 113, 20 atau 120, 23, dan 21 atau 25 atau 121) selalu mengembalikan abad (yyyy).
Penting
Secara default, SQL Server menginterpretasikan dua digit tahun berdasarkan tahun cutoff 2049. Itu berarti bahwa SQL Server menafsirkan tahun dua digit 49 sebagai 2049 dan tahun dua digit 50 sebagai 1950. Banyak aplikasi klien, termasuk yang didasarkan pada objek Automation, menggunakan tahun cutoff 2030. SQL Server menyediakan opsi konfigurasi cutoff dua digit tahun untuk mengubah tahun cutoff yang digunakan oleh SQL Server. Ini memungkinkan perawatan tanggal yang konsisten. Sebaiknya tentukan tahun empat digit.
3 Input saat Anda mengonversi ke datetime; output saat Anda mengonversi ke data karakter.
4 Dirancang untuk penggunaan XML. Untuk konversi dari datetime atau smalldatetime ke data karakter, lihat tabel sebelumnya untuk format output.
5 Hijriah adalah sistem kalender dengan beberapa variasi. SQL Server menggunakan algoritma Kuwaiti.
6 Untuk nilai milidetik (mmm) 0, nilai pecahan desimal milidetik tidak akan ditampilkan. Misalnya, nilai 2022-11-07T18:26:20.000 ditampilkan sebagai 2022-11-07T18:26:20.
7 Dalam gaya ini, mon mewakili representasi unicode Hijriah multi-token dari nama bulan penuh. Nilai ini tidak dirender dengan benar pada penginstalan SSMS AS default.
8 Hanya didukung saat mentransmisikan dari data karakter ke tanggalwaktu atau smalldatetime. Saat mentransmisikan data karakter yang hanya mewakili komponen tanggal atau hanya waktu ke jenis data tanggalwaktu atau smalldatetime , komponen waktu yang tidak ditentukan diatur ke 00:00:00.000, dan komponen tanggal yang tidak ditentukan diatur ke 1900-01-01.
9 Gunakan indikator Z zona waktu opsional untuk mempermudah pemetaan nilai tanggalwaktu XML yang memiliki informasi zona waktu ke nilai tanggalwaktu SQL Server yang tidak memiliki zona waktu. Z menunjukkan zona waktu pada UTC-0. Offset HH:MM , ke + arah atau - , menunjukkan zona waktu lainnya. Misalnya: 2022-12-12T23:45:12-08:00.
Saat mengonversi smalldatetime ke data karakter, gaya yang menyertakan detik atau milidetik menunjukkan nol dalam posisi ini. Saat mengonversi dari nilai datetime atau smalldatetime, gunakan panjang tipe data karakter atau varchar yang sesuai untuk memotong bagian tanggal yang tidak diinginkan.
Saat mengonversi data karakter menjadi datetimeoffset, menggunakan gaya yang menyertakan waktu, offset zona waktu ditambahkan ke hasilnya.
gaya float dan nyata
Untuk float atau ekspresi nyata, gaya bisa memiliki salah satu nilai yang diperlihatkan dalam tabel berikut. Nilai lain diproses sebagai 0.
| Nilai | Output |
|---|---|
| 0 (default) | Maksimal 6 digit. Gunakan dalam notasi ilmiah, jika sesuai. |
| 1 | Selalu 8 digit. Selalu gunakan dalam notasi ilmiah. |
| 2 | Selalu 16 digit. Selalu gunakan dalam notasi ilmiah. |
| 3 | Selalu 17 digit. Gunakan untuk konversi tanpa kerugian. Dengan gaya ini, setiap float atau nilai nyata yang berbeda dijamin untuk dikonversi menjadi string karakter yang berbeda. Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru, dan Azure SQL Database. |
| 126, 128, 129 | Disertakan karena alasan warisan. Jangan gunakan nilai-nilai ini untuk pengembangan baru. |
uang dan gaya smallmoney
Untuk uang atau ekspresi smallmoney, gaya bisa memiliki salah satu nilai yang diperlihatkan dalam tabel berikut. Nilai lain diproses sebagai 0.
| Nilai | Output |
|---|---|
| 0 (default) | Tidak ada koma setiap tiga digit di sebelah kiri titik desimal, dan dua digit di sebelah kanan titik desimal Contoh: 4235.98. |
| 1 | Koma setiap tiga digit di sebelah kiri titik desimal, dan dua digit di sebelah kanan titik desimal Contoh: 3.510,92. |
| 2 | Tidak ada koma setiap tiga digit di sebelah kiri titik desimal, dan empat digit di sebelah kanan titik desimal Contoh: 4235.9819. |
| 126 | Setara dengan gaya 2, saat mengonversi ke char(n) atau varchar(n) |
gaya xml
Untuk ekspresi xml, gaya bisa memiliki salah satu nilai yang diperlihatkan dalam tabel berikut ini. Nilai lain diproses sebagai 0.
| Nilai | Output |
|---|---|
| 0 (default) | Gunakan perilaku penguraian default yang membuang spasi kosong yang tidak signifikan, dan tidak memungkinkan subset DTD internal. Catatan: Saat mengonversi ke jenis data xml , ruang putih SQL Server yang tidak signifikan ditangani secara berbeda dari di XML 1.0. Untuk informasi selengkapnya, lihat Membuat Instans Data XML. |
| 1 | Pertahankan ruang putih yang tidak signifikan. Pengaturan gaya ini mengatur penanganan default xml:space agar sesuai dengan perilaku xml:space="preserve". |
| 2 | Aktifkan pemrosesan subset DTD internal terbatas. Jika diaktifkan, server dapat menggunakan informasi berikut yang disediakan dalam subset DTD internal, untuk melakukan operasi penguraian yang tidak valid. - Default untuk atribut diterapkan - Referensi entitas internal diselesaikan dan diperluas - DTD con mode tenda l diperiksa untuk kebenaran sinonis Pengurai mengabaikan subset DTD eksternal. Selain itu, ia tidak mengevaluasi deklarasi XML untuk melihat apakah atribut mandiri memiliki nilai ya atau tidak . Sebaliknya, ia mengurai instans XML sebagai dokumen yang berdiri sendiri. |
| 3 | Pertahankan ruang kosong yang tidak signifikan, dan aktifkan pemrosesan subset DTD internal terbatas. |
Gaya biner
Untuk ekspresi biner(n), char(n), varbinary(n), atau varchar(n), gaya dapat memiliki salah satu nilai yang diperlihatkan dalam tabel berikut. Nilai gaya yang tidak tercantum dalam tabel akan mengembalikan kesalahan.
| Nilai | Output |
|---|---|
| 0 (default) | Menerjemahkan karakter ASCII ke byte biner, atau byte biner ke karakter ASCII. Setiap karakter atau byte dikonversi 1:1. Untuk data_type biner, karakter 0x ditambahkan ke sebelah kiri hasil. |
| 1, 2 | Untuk data_type biner, ekspresi harus berupa ekspresi karakter. Ekspresi harus memiliki jumlah digit heksadesimal genap (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). Jika gaya diatur ke 1, ekspresi harus memiliki 0x sebagai dua karakter pertama. Jika ekspresi berisi jumlah karakter ganjil, atau jika salah satu karakter tidak valid, kesalahan akan muncul. Jika panjang ekspresi yang dikonversi melebihi panjang data_type, hasilnya dipotong dengan tepat. Panjang tetap data_typelebih besar dari hasil yang dikonversi memiliki nol yang ditambahkan ke sebelah kanan hasil. Data_type karakter jenis memerlukan ekspresi biner. Setiap karakter biner dikonversi menjadi dua karakter heksadesimal. Misalkan panjang ekspresi yang dikonversi melebihi panjang data_type. Dalam hal ini, terpotong. Untuk jenis karakter ukuran tetap data_type, jika panjang hasil yang dikonversi kurang dari panjang data_type, spasi ditambahkan ke sebelah kanan ekspresi yang dikonversi untuk mempertahankan jumlah digit heksadesimal yang merata. Karakter 0x tidak ditambahkan ke sebelah kiri hasil yang dikonversi untuk gaya 2. |
Konversi implisit
Konversi implisit tidak memerlukan spesifikasi CAST fungsi atau CONVERT fungsi. Konversi eksplisit memerlukan spesifikasi CAST fungsi atau CONVERT fungsi. Ilustrasi berikut menunjukkan semua konversi jenis data eksplisit dan implisit yang diizinkan untuk jenis data yang disediakan sistem SQL Server. Ini termasuk bigint, dan sql_variant, dan xml. Tidak ada konversi implisit pada penugasan dari jenis data sql_variant , tetapi ada konversi implisit ke sql_variant.
Tip
Pusat Unduhan Microsoft memiliki bagan ini yang tersedia untuk diunduh sebagai file PNG.
Bagan di atas menggambarkan semua konversi eksplisit dan implisit yang diizinkan di SQL Server, tetapi jenis data konversi yang dihasilkan tergantung pada operasi yang dilakukan:
- Untuk konversi eksplisit, pernyataan itu sendiri menentukan jenis data yang dihasilkan.
- Untuk konversi implisit, pernyataan penugasan seperti mengatur nilai variabel atau menyisipkan nilai ke dalam kolom akan menghasilkan jenis data yang ditentukan oleh deklarasi variabel atau definisi kolom.
- Untuk operator perbandingan atau ekspresi lain, jenis data yang dihasilkan akan bergantung pada aturan prioritas jenis data.
Tip
Contoh praktis pada efek prioritas jenis data dalam konversi dapat dilihat nanti di bagian ini.
Saat Anda mengonversi antara datetimeoffset dan karakter jenis karakter, nchar, nvarchar, dan varchar, bagian offset zona waktu yang dikonversi harus selalu memiliki digit ganda untuk dan HH MM. Contohnya,-08:00.
Karena data Unicode selalu menggunakan jumlah byte yang merata, berhati-hatilah saat Anda mengonversi biner atau varbinary ke atau dari jenis data yang didukung Unicode. Misalnya, konversi berikut tidak mengembalikan nilai heksadesimal 41. Ini mengembalikan nilai heksadesimal 4100:
SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary);
Untuk informasi selengkapnya, lihat Dukungan Kolate dan Unicode.
Jenis data bernilai besar
Jenis data bernilai besar memiliki perilaku konversi implisit dan eksplisit yang sama dengan rekan-rekan mereka yang lebih kecil - khususnya, jenis data nvarchar, varbinary, dan varchar . Namun, pertimbangkan panduan berikut:
- Konversi dari gambar ke varbinary(maks), dan sebaliknya, beroperasi sebagai konversi implisit, seperti halnya konversi antara teks dan varchar(maks), dan ntext dan nvarchar(maks).
- Konversi dari jenis data bernilai besar, seperti varchar(max), ke jenis data mitra yang lebih kecil, seperti varchar, adalah konversi implisit, tetapi pemotongan terjadi jika ukuran nilai besar melebihi panjang yang ditentukan dari jenis data yang lebih kecil.
- Konversi dari nvarchar, varbinary, atau varchar ke jenis data bernilai besar yang sesuai terjadi secara implisit.
- Konversi dari jenis data sql_variant ke jenis data bernilai besar adalah konversi eksplisit.
- Jenis data bernilai besar tidak dapat dikonversi ke jenis data sql_variant .
Untuk informasi selengkapnya tentang konversi dari jenis data xml , lihat Membuat Instans Data XML.
jenis data xml
Saat Anda secara eksplisit atau implisit mentransmisikan jenis data xml ke string atau jenis data biner, konten tipe data xml diserialisasikan berdasarkan sekumpulan aturan yang ditentukan. Untuk informasi tentang aturan ini, lihat Menentukan Serialisasi Data XML. Untuk informasi tentang konversi dari jenis data lain ke jenis data xml , lihat Membuat Instans Data XML.
jenis data teks dan gambar
Jenis data teks dan gambar tidak mendukung konversi jenis data otomatis. Anda dapat secara eksplisit mengonversi data teks menjadi data karakter, dan data gambar menjadi biner atau varbinary, tetapi panjang maksimumnya adalah 8000 byte. Jika Anda mencoba konversi yang salah, misalnya mencoba mengonversi ekspresi karakter yang menyertakan huruf ke int, SQL Server mengembalikan pesan kesalahan.
Kolatasi output
CAST Ketika fungsi atau CONVERT menghasilkan string karakter, dan mereka menerima input string karakter, output memiliki label kolase dan kolase yang sama dengan input. Jika input bukan string karakter, output memiliki kolater default database, dan label kolaterasi coercible-default. Untuk informasi selengkapnya, lihat Prioritas Kolase (Transact-SQL).
Untuk menetapkan kolate yang berbeda ke output, terapkan klausa COLLATE ke ekspresi CAST hasil fungsi atau CONVERT . Contohnya:
SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS;
Memotong dan membulatkan hasil
Saat mengonversi karakter atau ekspresi biner (biner, char, nchar, nvarchar, varbinary, atau varchar) ke ekspresi jenis data yang berbeda, operasi konversi dapat memotong data output, hanya menampilkan sebagian data output, atau mengembalikan kesalahan. Kasus-kasus ini akan terjadi jika hasilnya terlalu pendek untuk ditampilkan. Konversi ke biner, char, nchar, nvarchar, varbinary, atau varchar dipotong, kecuali untuk konversi yang ditunjukkan dalam tabel berikut.
| Dari jenis data | Ke tipe data | Hasil |
|---|---|---|
| int, smallint, atau tinyint | Char varchar |
Terlalu pendek untuk ditampilkan |
| nchar nvarchar |
Kesalahan 1 | |
| uang, smallmoney, numerik, desimal, float, atau nyata | Char varchar |
Kesalahan 1 |
| nchar nvarchar |
Kesalahan 1 |
1 Kesalahan dikembalikan karena panjang hasil terlalu pendek untuk ditampilkan.
SQL Server menjamin bahwa hanya konversi pulang pergi, dengan kata lain konversi yang mengonversi jenis data dari jenis data aslinya dan kembali lagi, menghasilkan nilai yang sama dari versi ke versi. Contoh berikut menunjukkan konversi pulang pergi seperti itu:
DECLARE @myval DECIMAL(5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS VARBINARY(20)) AS DECIMAL(10, 5));
-- Or, using CONVERT
SELECT CONVERT(DECIMAL(10, 5), CONVERT(VARBINARY(20), @myval));
GO
Peringatan
Jangan buat nilai biner , lalu konversikan ke jenis data kategori tipe data numerik. SQL Server tidak menjamin bahwa hasil konversi jenis data desimal atau numerik , ke biner, akan sama antara versi SQL Server.
Contoh berikut menunjukkan ekspresi yang dihasilkan yang terlalu kecil untuk ditampilkan.
USE AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
SUBSTRING(p.Title, 1, 25) AS Title,
CAST(e.SickLeaveHours AS CHAR(1)) AS [Sick Leave]
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT e.BusinessEntityID > 5;
GO
Berikut set hasilnya.
FirstName LastName Title Sick Leave
--------- ------------- ------- --------`
Ken Sanchez NULL *
Terri Duffy NULL *
Roberto Tamburello NULL *
Rob Walters NULL *
Gail Erickson Ms. *
(5 row(s) affected)
Ketika Anda mengonversi jenis data yang berbeda di tempat desimal, SQL Server terkadang akan mengembalikan nilai hasil yang dipotong, dan di lain waktu akan mengembalikan nilai bulat. Tabel ini memperlihatkan perilaku.
| Dari | Untuk | Perilaku |
|---|---|---|
| numerik | numerik | Round |
| numerik | int | Truncate |
| numerik | uang | Round |
| uang | int | Round |
| uang | numerik | Round |
| float | int | Truncate |
| float | numerik | Ronde 1 |
| float | datetime | Round |
| datetime | int | Round |
1 Konversi nilai float yang menggunakan notasi ilmiah ke desimal atau numerik dibatasi untuk nilai presisi 17 digit saja. Nilai apa pun dengan presisi lebih tinggi dari 17 putaran menjadi nol.
Misalnya, nilai 10,6496 dan -10,6496 dapat dipotong atau dibulatkan selama konversi ke jenis int atau numerik :
SELECT CAST(10.6496 AS INT) AS trunc1,
CAST(-10.6496 AS INT) AS trunc2,
CAST(10.6496 AS NUMERIC) AS round1,
CAST(-10.6496 AS NUMERIC) AS round2;
Hasil kueri diperlihatkan dalam tabel berikut ini:
| pemotongan1 | trunc2 | round1 | round2 |
|---|---|---|---|
| 10 | 10- | 11 | -11 |
Saat mengonversi jenis data di mana jenis data target memiliki lebih sedikit tempat desimal daripada jenis data sumber, nilai dibulatkan. Misalnya, konversi ini mengembalikan $10.3497:
SELECT CAST(10.3496847 AS money);
SQL Server mengembalikan pesan kesalahan saat mengonversi data karakter nonnumerik, nchar, nvarchar, atau varchar menjadi desimal, float, int, numerik. SQL Server juga mengembalikan kesalahan ketika string kosong (" ") dikonversi menjadi numerik atau desimal.
Konversi tanggalwaktu tertentu tidak deterministik
Gaya yang konversi string-ke-tanggalwaktunya nondeterministik adalah sebagai berikut:
- Semua gaya di bawah 100 1
- 106
- 107
- 109
- 113
- 130
1 Dengan pengecualian gaya 20 dan 21
Untuk informasi selengkapnya, lihat Konversi nondeterministik string tanggal harfiah menjadi nilai DATE.
Karakter tambahan (pasangan pengganti)
Dimulai dengan SQL Server 2012 (11.x), saat menggunakan kolase CAST karakter tambahan (SC), operasi dari nchar atau nvarchar ke jenis nchar atau nvarchar dengan panjang yang lebih kecil tidak akan terpotong di dalam pasangan pengganti. Sebagai gantinya, operasi memotong sebelum karakter tambahan. Misalnya, fragmen kode berikut hanya menyimpan @x 'ab'. Tidak ada cukup ruang untuk menahan karakter tambahan.
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST(@x AS NVARCHAR(3));
Saat menggunakan kolatasi SC, perilaku CONVERT, dianalogikan dengan CAST. Untuk informasi selengkapnya, lihat Dukungan Kolate dan Unicode - Karakter Tambahan.
Dukungan kompatibilitas
Dalam versi SQL Server sebelumnya, gaya default untuk CAST dan CONVERT operasi pada waktu dan jenis data datetime2 adalah 121, kecuali ketika salah satu jenis digunakan dalam ekspresi kolom komputasi. Untuk kolom komputasi, gaya defaultnya adalah 0. Perilaku ini berdampak pada kolom komputasi saat dibuat, digunakan dalam kueri yang melibatkan parameterisasi otomatis, atau digunakan dalam definisi batasan.
Di bawah tingkat kompatibilitas 110 dan yang lebih tinggi, CAST operasi dan CONVERT pada jenis data waktu dan tanggalwaktu2 selalu memiliki 121 sebagai gaya default. Jika kueri bergantung pada perilaku lama, gunakan tingkat kompatibilitas kurang dari 110, atau tentukan gaya 0 secara eksplisit dalam kueri yang terpengaruh.
| Nilai tingkat kompatibilitas | Gaya default untuk CAST dan CONVERT 1 |
Gaya default untuk kolom komputasi |
|---|---|---|
| <110 | 121 | 0 |
| > = 110 | 121 | 121 |
1 Kecuali untuk kolom komputasi
Meningkatkan database ke tingkat kompatibilitas 110 dan yang lebih tinggi tidak akan mengubah data pengguna yang telah disimpan ke disk. Anda harus memperbaiki data ini secara manual sebagaimana mestinya. Misalnya, jika Anda menggunakan SELECT INTO untuk membuat tabel dari sumber yang berisi ekspresi kolom komputasi yang dijelaskan di atas, data (menggunakan gaya 0) akan disimpan daripada definisi kolom komputasi itu sendiri. Anda harus memperbarui data ini secara manual agar sesuai dengan gaya 121.
Contoh
J. Gunakan CAST dan CONVERT
Contoh-contoh ini mengambil nama produk, untuk produk yang memiliki 3 sebagai digit pertama harga daftar, dan mengonversi nilainya ListPrice menjadi int.
Gunakan CAST:
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS INT) LIKE '33%';
GO
Gunakan CONVERT:
USE AdventureWorks2022;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName,
ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '33%';
GO
Berikut set hasilnya. Kumpulan hasil sampel sama untuk dan CAST CONVERT.
ProductName ListPrice
------------------------------ ---------------------
LL Road Frame - Black, 58 337.22
LL Road Frame - Black, 60 337.22
LL Road Frame - Black, 62 337.22
LL Road Frame - Red, 44 337.22
LL Road Frame - Red, 48 337.22
LL Road Frame - Red, 52 337.22
LL Road Frame - Red, 58 337.22
LL Road Frame - Red, 60 337.22
LL Road Frame - Red, 62 337.22
LL Road Frame - Black, 44 337.22
LL Road Frame - Black, 48 337.22
LL Road Frame - Black, 52 337.22
Mountain-100 Black, 38 3374.99
Mountain-100 Black, 42 3374.99
Mountain-100 Black, 44 3374.99
Mountain-100 Black, 48 3374.99
HL Road Front Wheel 330.06
LL Touring Frame - Yellow, 62 333.42
LL Touring Frame - Blue, 50 333.42
LL Touring Frame - Blue, 54 333.42
LL Touring Frame - Blue, 58 333.42
LL Touring Frame - Blue, 62 333.42
LL Touring Frame - Yellow, 44 333.42
LL Touring Frame - Yellow, 50 333.42
LL Touring Frame - Yellow, 54 333.42
LL Touring Frame - Yellow, 58 333.42
LL Touring Frame - Blue, 44 333.42
HL Road Tire 32.60
(28 rows affected)
B. Menggunakan CAST dengan operator aritmatika
Contoh ini menghitung komputasi kolom tunggal (Computed) dengan memba lagi total penjualan tahun ke tanggal (SalesYTD) dengan persentase komisi (CommissionPCT). Nilai ini dibulatkan ke bilangan bulat terdekat dan kemudian CAST ke jenis data int .
USE AdventureWorks2022;
GO
SELECT CAST(ROUND(SalesYTD / CommissionPCT, 0) AS INT) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
Berikut set hasilnya.
Computed
------
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107
(14 row(s) affected)
C. Menggunakan CAST untuk menggabungkan
Contoh ini menggabungkan ekspresi noncharacter dengan menggunakan CAST. Ini menggunakan AdventureWorksDW2022 database.
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
Berikut set hasilnya.
ListPrice
------------------------
The list price is 357.06
The list price is 364.09
The list price is 364.09
The list price is 364.09
The list price is 364.09
D. Gunakan CAST untuk menghasilkan teks yang lebih mudah dibaca
Contoh ini menggunakan CAST dalam daftar SELECT, untuk mengonversi Name kolom menjadi kolom karakter(10). Ini menggunakan AdventureWorksDW2022 database.
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name,
ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
GO
Berikut set hasilnya.
Name ListPrice
---------- ---------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99
E. Menggunakan CAST dengan klausa LIKE
Contoh ini mengonversi money nilai kolom SalesYTD menjadi int jenis data, lalu ke tipe data karakter(20), sehingga LIKE klausa dapat menggunakannya.
USE AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
s.SalesYTD,
s.BusinessEntityID
FROM Person.Person AS p
INNER JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS INT) AS CHAR(20)) LIKE '2%';
GO
Berikut set hasilnya.
FirstName LastName SalesYTD BusinessEntityID
---------------- ------------------- ---------------- -------------
Tsvi Reiter 2811012.7151 279
Syed Abbas 219088.8836 288
Rachel Valdez 2241204.0424 289
(3 row(s) affected)
F. Menggunakan CONVERT atau CAST dengan XML yang diketik
Contoh-contoh ini menunjukkan penggunaan CONVERT untuk mengonversi data ke XML yang diketik, dengan menggunakan Tipe dan Kolom Data XML (SQL Server).
Contoh ini mengonversi string dengan spasi kosong, teks, dan markup menjadi XML yang ditik, dan menghapus semua spasi putih yang tidak signifikan (spasi kosong batas antar simpul):
SELECT CONVERT(XML, '<root><child/></root>')
Contoh ini mengonversi string serupa dengan spasi kosong, teks, dan markup menjadi XML yang ditik dan mempertahankan spasi putih yang tidak signifikan (spasi kosong batas antar simpul):
SELECT CONVERT(XML, '<root> <child/> </root>', 1)
Contoh ini melemparkan string dengan spasi kosong, teks, dan markup ke dalam XML yang diketik:
SELECT CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML)
Lihat Membuat Instans Data XML untuk contoh selengkapnya.
G. Menggunakan CAST dan CONVERT dengan data tanggalwaktu
Dimulai dengan GETDATE() nilai, contoh ini menampilkan tanggal dan waktu saat ini, menggunakan CAST untuk mengubah tanggal dan waktu saat ini menjadi tipe data karakter, lalu menggunakan CONVERT untuk menampilkan tanggal dan waktu dalam ISO 8601 format.
SELECT GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), GETDATE(), 126) AS UsingConvertTo_ISO8601;
GO
Berikut set hasilnya.
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ---------------------- ------------------------------
2022-04-18 09:58:04.570 Apr 18 2022 9:58AM 2022-04-18T09:58:04.570
(1 row(s) affected)
Contoh ini kira-kira kebalikan dari contoh sebelumnya. Contoh ini menampilkan tanggal dan waktu sebagai data karakter, menggunakan CAST untuk mengubah data karakter ke jenis data tanggalwaktu , lalu menggunakan CONVERT untuk mengubah data karakter ke jenis data tanggalwaktu .
SELECT '2006-04-25T15:50:59.997' AS UnconvertedText,
CAST('2006-04-25T15:50:59.997' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601;
GO
Berikut set hasilnya.
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997
(1 row(s) affected)
H. Menggunakan CONVERT dengan data biner dan karakter
Contoh-contoh ini menunjukkan hasil konversi data biner dan karakter, menggunakan gaya yang berbeda.
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(CHAR(8), 0x4E616d65, 0) AS [Style 0, binary to character];
Berikut set hasilnya.
Style 0, binary to character
----------------------------
Name
(1 row(s) affected)
Contoh ini menunjukkan bahwa Gaya 1 dapat memaksa pemotongan hasil. Karakter 0x dalam kumpulan hasil memaksa pemotongan.
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];
Berikut set hasilnya.
Style 1, binary to character
------------------------------
0x4E616D
(1 row(s) affected)
Contoh ini menunjukkan bahwa Gaya 2 tidak memotong hasilnya, karena hasilnya tidak menyertakan karakter 0x.
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];
Berikut set hasilnya.
Style 2, binary to character
------------------------------
4E616D65
(1 row(s) affected)
Konversikan nilai karakter 'Nama' menjadi nilai biner.
SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];
Berikut set hasilnya.
Style 0, character to binary
----------------------------
0x4E616D6500000000
(1 row(s) affected)
SELECT CONVERT(BINARY(4), '0x4E616D65', 1) AS [Style 1, character to binary];
Berikut set hasilnya.
Style 1, character to binary
----------------------------
0x4E616D65
(1 row(s) affected)
SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];
Berikut set hasilnya.
Style 2, character to binary
----------------------------------
0x4E616D65
(1 row(s) affected)
I. Mengonversi jenis data tanggal dan waktu
Contoh ini menunjukkan konversi jenis data tanggal, waktu, dan tanggalwaktu .
DECLARE @d1 DATE,
@t1 TIME,
@dt1 DATETIME;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [DATE],
CAST(@d1 AS DATETIME) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [TIME],
CAST(@t1 AS DATETIME) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [DATETIME],
CAST(@dt1 AS DATE) AS [datetime as date],
CAST(@dt1 AS TIME) AS [datetime as time];
Pastikan nilai berada dalam rentang yang kompatibel saat mempertimbangkan konversi dari tanggal ke tanggalwaktu atau datetime2. Nilai tahun minimum untuk tanggalwaktu adalah 1753, sedangkan nilai tahun minimum adalah 0001 untuk tanggal dan tanggalwaktu2.
DECLARE @d1 DATE, @dt1 DATETIME , @dt2 DATETIME2
SET @d1 = '1492-08-03'
--This is okay; Minimum YYYY for DATE is 0001
SET @dt2 = CAST(@d1 AS DATETIME2)
--This is okay; Minimum YYYY for DATETIME2 IS 0001
SET @dt1 = CAST(@d1 AS DATETIME)
--This will error with (Msg 242) "The conversion of a date data type to a datetime data type resulted in an out-of-range value."
--Minimum YYYY for DATETIME is 1753
j. Menggunakan CONVERT dengan data tanggalwaktu dalam format yang berbeda
Dimulai dengan GETDATE() nilai, contoh ini menggunakan CONVERT untuk menampilkan semua gaya tanggal dan waktu di bagian Gaya tanggal dan Waktu artikel ini.
| Format # | Kueri contoh | Hasil sampel |
|---|---|---|
| 0 | SELECT CONVERT(NVARCHAR, GETDATE(), 0) |
Agt 23 2019 13:39SORE |
| 1 | SELECT CONVERT(NVARCHAR, GETDATE(), 1) |
08/23/19 |
| 2 | SELECT CONVERT(NVARCHAR, GETDATE(), 2) |
19.08.23 |
| 3 | SELECT CONVERT(NVARCHAR, GETDATE(), 3) |
23/08/19 |
| 4 | SELECT CONVERT(NVARCHAR, GETDATE(), 4) |
23.08.19 |
| 5 | SELECT CONVERT(NVARCHAR, GETDATE(), 5) |
23-08-19 |
| 6 | SELECT CONVERT(NVARCHAR, GETDATE(), 6) |
23 Agu 19 |
| 7 | SELECT CONVERT(NVARCHAR, GETDATE(), 7) |
23 Agu, 19 |
| 8 atau 24 atau 108 | SELECT CONVERT(NVARCHAR, GETDATE(), 8) |
13:39:17 |
| 9 atau 109 | SELECT CONVERT(NVARCHAR, GETDATE(), 9) |
23 Agu 2019 1:39:17:090PM |
| 10 | SELECT CONVERT(NVARCHAR, GETDATE(), 10) |
08-23-19 |
| 11 | SELECT CONVERT(NVARCHAR, GETDATE(), 11) |
19/08/23 |
| 12 | SELECT CONVERT(NVARCHAR, GETDATE(), 12) |
190823 |
| 13 atau 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 13) |
23 Agu 2019 13:39:17:090 |
| 14 atau 114 | SELECT CONVERT(NVARCHAR, GETDATE(), 14) |
13:39:17:090 |
| 20 atau 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 20) |
2019-08-23 13:39:17 |
| 21 atau 25 atau 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 21) |
2019-08-23 13:39:17.090 |
| 22 | SELECT CONVERT(NVARCHAR, GETDATE(), 22) |
08/23/19 13:39:17 PM |
| 23 | SELECT CONVERT(NVARCHAR, GETDATE(), 23) |
2019-08-23 |
| 101 | SELECT CONVERT(NVARCHAR, GETDATE(), 101) |
08/23/2019 |
| 102 | SELECT CONVERT(NVARCHAR, GETDATE(), 102) |
2019.08.23 |
| 103 | SELECT CONVERT(NVARCHAR, GETDATE(), 103) |
23/08/2019 |
| 104 | SELECT CONVERT(NVARCHAR, GETDATE(), 104) |
23.08.2019 |
| 105 | SELECT CONVERT(NVARCHAR, GETDATE(), 105) |
23-08-2019 |
| 106 | SELECT CONVERT(NVARCHAR, GETDATE(), 106) |
23 Agu 2019 |
| 107 | SELECT CONVERT(NVARCHAR, GETDATE(), 107) |
23 Agu 2019 |
| 110 | SELECT CONVERT(NVARCHAR, GETDATE(), 110) |
08-23-2019 |
| 111 | SELECT CONVERT(NVARCHAR, GETDATE(), 111) |
2019/08/23 |
| 112 | SELECT CONVERT(NVARCHAR, GETDATE(), 112) |
20190823 |
| 113 | SELECT CONVERT(NVARCHAR, GETDATE(), 113) |
23 Agu 2019 13:39:17.090 |
| 120 | SELECT CONVERT(NVARCHAR, GETDATE(), 120) |
2019-08-23 13:39:17 |
| 121 | SELECT CONVERT(NVARCHAR, GETDATE(), 121) |
2019-08-23 13:39:17.090 |
| 126 | SELECT CONVERT(NVARCHAR, GETDATE(), 126) |
2019-08-23T13:39:17.090 |
| 127 | SELECT CONVERT(NVARCHAR, GETDATE(), 127) |
2019-08-23T13:39:17.090 |
| 130 | SELECT CONVERT(NVARCHAR, GETDATE(), 130) |
22 ذو الحجة 1440 1:39:17.090P |
| 131 | SELECT CONVERT(NVARCHAR, GETDATE(), 131) |
22/12/1440 1:39:17.090PM |
K. Efek prioritas jenis data dalam konversi yang diizinkan
Contoh berikut mendefinisikan variabel jenis varchar(10), menetapkan nilai bilangan bulat ke variabel, lalu memilih perangkaian variabel dengan string.
DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.' AS Result
Berikut set hasilnya.
Result
-----------------------
1 is a string.
Nilai int 1 dikonversi ke varchar.
Contoh ini memperlihatkan kueri serupa, menggunakan variabel int sebagai gantinya:
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.' AS Result
Dalam hal ini, pernyataan SELECT akan melemparkan kesalahan berikut:
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ' is not a string.' to data type int.
Untuk mengevaluasi ekspresi @notastring + ' is not a string.', SQL Server perlu mengikuti aturan prioritas jenis data untuk menyelesaikan konversi implisit sebelum hasil ekspresi dapat dihitung. Karena int memiliki prioritas yang lebih tinggi daripada varchar, SQL Server mencoba mengonversi string menjadi bilangan bulat dan gagal karena string ini tidak dapat dikonversi menjadi bilangan bulat.
Jika kita memberikan string yang dapat dikonversi, pernyataan akan berhasil, seperti yang terlihat dalam contoh berikut:
DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'
Dalam hal ini, string '1' dapat dikonversi ke nilai bilangan bulat 1, sehingga pernyataan SELECT ini akan mengembalikan nilai 2. Ketika jenis data yang disediakan adalah bilangan bulat, operator + menjadi operator matematika tambahan, bukan perangkaian string.
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
L. Menggunakan CAST dan CONVERT
Contoh ini mengambil nama produk untuk produk yang memiliki 3 di digit pertama harga daftar mereka, dan mengonversi ListPrice produk ini menjadi int. Ini menggunakan AdventureWorksDW2022 database.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
Contoh ini memperlihatkan kueri yang sama, menggunakan CONVERT alih-alih CAST. Ini menggunakan AdventureWorksDW2022 database.
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CONVERT(INT, ListPrice) LIKE '3%';
M. Menggunakan CAST dengan operator aritmatika
Contoh ini menghitung nilai kolom tunggal dengan membalai harga satuan produk (UnitPrice) dengan persentase diskon (UnitPriceDiscountPct). Hasil ini kemudian dibulatkan ke bilangan bulat terdekat, dan akhirnya dikonversi ke jenis data int. Contoh ini menggunakan AdventureWorksDW2022 database.
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
Berikut set hasilnya.
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice
---------- --------- -------------------- -------------
323 430.6445 0.05 22
213 18.5043 0.05 1
456 37.4950 0.10 4
456 37.4950 0.10 4
216 18.5043 0.05 1
N. Menggunakan CAST dengan klausa LIKE
Contoh ini mengonversi kolom ListPrice uang ke jenis int, lalu ke jenis karakter(20), sehingga klausa LIKE dapat menggunakannya. Contoh ini menggunakan AdventureWorksDW2022 database.
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
O. Menggunakan CAST dan CONVERT dengan data tanggalwaktu
Contoh ini menampilkan tanggal dan waktu saat ini, menggunakan CAST untuk mengubah tanggal dan waktu saat ini menjadi jenis data karakter, dan akhirnya menggunakan CONVERT tampilan tanggal dan waktu dalam format ISO 8601. Contoh ini menggunakan AdventureWorksDW2022 database.
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast,
CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
Berikut set hasilnya.
UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
--------------------- --------------------------- ---------------------------
07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025
Contoh ini adalah kebalikan kasar dari contoh sebelumnya. Contoh ini menampilkan tanggal dan waktu sebagai data karakter, menggunakan CAST untuk mengubah data karakter ke jenis data tanggalwaktu , lalu menggunakan CONVERT untuk mengubah data karakter ke jenis data tanggalwaktu . Contoh ini menggunakan AdventureWorksDW2022 database.
SELECT TOP(1)
'2010-07-25T13:50:38.544' AS UnconvertedText,
CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast,
CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom_ISO8601
FROM dbo.DimCustomer;
Berikut set hasilnya.
UnconvertedText UsingCast UsingConvertFrom_ISO8601
----------------------- ----------------------- ------------------------
2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM
Lihat juga
- FORMAT (T-SQL)
- STR (T-SQL)
- SELECT (Transact-SQL)
- Fungsi Sistem (Transact-SQL)
- Dukungan Kolajeasi dan Unicode