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;