CONVERT DATATYPE SQL
Các cách đổi kiểu dữ liệu trong SQL SERVER
KIỂU DATETIME
Đổi kiểu Date sang Text
CONVERT(nvarchar, VR.Ngay, 103) --UK Date format 'DD/MM/YYYY'
--Kết quả:
05/11/2022Format # Example query Sample result
0 SELECT CONVERT(NVARCHAR, GETDATE(), 0) Aug 23 2019 1:39PM
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 Aug 19
7 SELECT CONVERT(NVARCHAR, GETDATE(), 7) Aug 23, 19
8 || 24 || 108 SELECT CONVERT(NVARCHAR, GETDATE(), 8) 13:39:17
9 || 109 SELECT CONVERT(NVARCHAR, GETDATE(), 9) Aug 23 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 || 113 SELECT CONVERT(NVARCHAR, GETDATE(), 13) 23 Aug 2019 13:39:17:090
14 || 114 SELECT CONVERT(NVARCHAR, GETDATE(), 14) 13:39:17:090
20 || 120 SELECT CONVERT(NVARCHAR, GETDATE(), 20) 2019-08-23 13:39:17
21 || 25 || 121 SELECT CONVERT(NVARCHAR, GETDATE(), 21) 2019-08-23 13:39:17.090
22 SELECT CONVERT(NVARCHAR, GETDATE(), 22) 08/23/19 1: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 Aug 2019
107 SELECT CONVERT(NVARCHAR, GETDATE(), 107) Aug 23, 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 Aug 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
Query
Kết quả
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
21/03/2021
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date
21/03/2021, 11:36:14
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date
Wednesday, March, 2021
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date
Mar 21 2021
SELECT FORMAT (getdate(), 'MM.dd.yy') as date
03.21.21
SELECT FORMAT (getdate(), 'MM-dd-yy') as date
03-21-21
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date
11:36:14 AM
SELECT FORMAT (getdate(), 'd','us') as date
03/21/2021
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date
2021-03-21 11:36:14 AM
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date
2021.03.21 11:36:14 A
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date --Spanish
domingo, marzo, 2021
SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date --Japanese
日曜日 21, 3月, 2021
SELECT FORMAT (getdate(), 'MM-dd-yyyy ') as date
03-21-2021
SELECT FORMAT (getdate(), 'MM dd yyyy ') as date
03 21 2021
SELECT FORMAT (getdate(), 'yyyyMMdd') as date
20231011
SELECT FORMAT (getdate(), 'HH:mm:dd') as time
11:36:14
SELECT FORMAT (getdate(), 'HH:mm:dd.ffffff') as time
11:36:14.84000
Đổi kiểu Date sang số Numeric
declare @dateb datetime
set @dateb = getdate()
select cast(format(@dateb,'yyyyMMdd') as int) --2012 or higher
select cast(convert(varchar(8),@dateb,112) as int) -- all versions
-------------------------------------------------------------------------
select convert(decimal(12,0),getdate())
select convert(decimal(12,4),getdate())
select convert(NUMERIC(12,0),getdate())
select convert(NUMERIC(12,4),getdate())
-- Kết quả:
45603
--
45602.6502
KIỂU SỐ NUMERIC
Đổi kiểu Numeric sang Text
--CAST (expression AS data_type [(length)])
SELECT CAST(123 AS VARCHAR(10))
--------------------------------------
--CONVERT (data_type [ (length) ], expression [, style ])
SELECT CONVERT(VARCHAR(10), 123456)
--------------------------------------
--STR(number, length, decimal)
SELECT STR(987654, 10, 0)
-- Thay thế str bằng hàm LTRIM() sẽ giữ nguyên giá trị
-- Ví dụ
SELECT STR(1234567890.123)
-- Kết quả:
1234567890
-- Dùng LTRIM()
SELECT LTRIM(1234567890.123)
--Kết quả:
1234567890.123
Đổi kiểu Numeric sang Date
--
SELECT CAST( CAST( 20281030 AS char(8)) AS date );
--Kết quả:
2028-10-30
--
SELECT DATEADD(s, 1870917359, '1970-01-01');
--Kết quả:
2029-04-15 03:15:59.000
--
SELECT CAST(49723.3986 AS datetime);
--Kết quả:
2036-02-20 09:33:59.040
-- Đổi kiểu ngầm định
DECLARE @number float = 49723.3986;
DECLARE @datetime datetime = @number;
SELECT
@number AS "Original Value",
@datetime AS "Converted Value";
--Kết quả:
Original Value Converted Value
-------------- -----------------------
49723.3986 2036-02-20 09:33:59.040
KIỂU TEXT
Đổi kiểu Text sang Date
-- Dùng CAST
SELECT CAST('11/08/2024' as date) as StringToDate ,
CAST(GETDATE() as VARCHAR(50)) as DateToString
--Kết quả:
2024-11-08 Nov 8 2024 4:00PM
-- Dùng CONVERT
SELECT CONVERT(DATETIME,'11/08/2024',103)
--Kết quả:
2024-08-11 00:00:00.000
-- Dùng PARSE:
--PARSE(<value> AS <data type> [USING <culture>])
SELECT PARSE('11/08/2024' AS DATE USING 'AR-LB')
--Kết quả:
2024-08-11
-- Dùng TRY_CAST(), TRY_CONVERT() and TRY_PARSE() tương tựĐổi kiểu Text sang Numeric
-- Dùng CAST
SELECT CAST('100' AS INT) AS converted_integer
-- Dùng CONVERT
SELECT CONVERT(INT, '2000') AS converted_integer
-- DÙNG TRY_CAST
--Cách 1:
SELECT TRY_CAST(your_text_column AS INT) AS converted_integerFROM your_table;
--Cách 2:
SELECT CASE WHEN ISNUMERIC(your_text_column) = 1 THEN CAST(your_text_column AS INT) ELSE NULL END AS converted_integer
FROM your_table;
-- Cách 3: Kiểm soát được lỗi
BEGIN TRY
SELECT CAST(your_text_column AS INT) AS converted_integer FROM your_table;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
TÀI LIỆU THAM KHẢO
https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
https://www.w3schools.com/sql/func_sqlserver_current_user.asp
https://www.sqlshack.com/sql-server-functions-for-converting-string-to-date/
https://www.sqlshack.com/sql-convert-date-functions-and-formats/Last updated