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/2022

Format #	    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/