# 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/ ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://kythuat.dtechvn.com/sql-server/convert-datatype-sql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.