> For the complete documentation index, see [llms.txt](https://kythuat.dtechvn.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kythuat.dtechvn.com/sql-server/convert-datatype-sql.md). # CONVERT DATATYPE SQL ## KIỂU DATETIME ### Đổi kiểu Date sang Text ```sql CONVERT(nvarchar, VR.Ngay, 103) --UK Date format 'DD/MM/YYYY' --Kết quả: 05/11/2022 ``` ```sql 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 ```
QueryKết quả
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date21/03/2021
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date21/03/2021, 11:36:14
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as dateWednesday, March, 2021
SELECT FORMAT (getdate(), 'MMM dd yyyy') as dateMar 21 2021
SELECT FORMAT (getdate(), 'MM.dd.yy') as date03.21.21
SELECT FORMAT (getdate(), 'MM-dd-yy') as date03-21-21
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date11:36:14 AM
SELECT FORMAT (getdate(), 'd','us') as date03/21/2021
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date2021-03-21 11:36:14 AM
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date2021.03.21 11:36:14 A
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date --Spanishdomingo, marzo, 2021
SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date --Japanese日曜日 21, 3月, 2021
SELECT FORMAT (getdate(), 'MM-dd-yyyy ') as date03-21-2021
SELECT FORMAT (getdate(), 'MM dd yyyy ') as date03 21 2021
SELECT FORMAT (getdate(), 'yyyyMMdd') as date20231011
SELECT FORMAT (getdate(), 'HH:mm:dd') as time11:36:14
SELECT FORMAT (getdate(), 'HH:mm:dd.ffffff') as time11:36:14.84000
### Đổi kiểu Date sang số Numeric ```sql 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 ```sql --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 ```sql -- 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 ```sql -- 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( AS [USING ]) 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 ```sql -- 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/ ```