- CONCAT function : CONCAT()
- Format function : FORMAT()
- Logical Functions : IIF & CHOOSE()
- Conversion functions: TRY_CONVERT(), TRY_PARSE()
- Date Functions: DATEFROMPARTS(), TIMEFORMPARTS(), EOMONTH(), PARSE()
CONCAT
CONCAT function will ignore the NULL value.
SELECT CONCAT ( 'Happy ', 'Birthday ', 'Skye', 9, '/', '24' ) AS Result;
Create table #temp
( name nvarchar(200) NOT NULL,
middle_name nvarchar(200) NULL,
last_name nvarchar(200) NOT NULL );
INSERT INTO #temp VALUES('Summer','Tiger','Chen');
INSERT INTO #temp VALUES('Sunny','Dragon','Chen');
INSERT INTO #temp VALUES('Skye',NULL,'Chen');
select CONCAT( name,middle_name,last_name) AS RESULT from #temp;
DROP table #temp;
FORMAT
DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS Result;
SET @d = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result;
IIF & CHOOSE
IIF is the quicker way for CASE statement.
--- CHOOSE & IIF
SELECT CHOOSE ( 2, 'MD', 'ED', 'VP', 'Associate' ) AS Result;
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
TRY_CONVERT
--- TRY_CONVERT
--- If convert fail, it will return NULL
SELECT
CASE WHEN TRY_CONVERT(float,'test') IS NULL
THEN 'Convert failed'
ELSE 'Convert succeeded'
END AS Result
GO
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2,'12/31/2010') AS Result;
GO
SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2,'12/31/2010') AS Result;
GO
TRY_PARSE
SELECT
CASE WHEN TRY_PARSE('test' AS float) IS NULL
THEN 'Parse failed'
ELSE 'Parse succeeded'
END AS Result
GO
SELECT TRY_PARSE('12/31/2010' AS datetime2 USING 'en-US') AS Result;
GO
DATEFROMPARTS
DATEFROMPARTS use to format the date. If the input string is not the valid date. It will split the error.
Msg 289, Level 16, State 1, Line 46
Cannot construct data type date, some of the arguments have values which are not valid.
--- DATEFROMPARTS
SELECT DATEFROMPARTS ( 2014, 9, 31 ) AS Result;
GO
SELECT DATEFROMPARTS ( 2014, 9, 30 ) AS Result;
GO
TIMEFORMPARTS()
TIMEFORMPARTS() use to format the time. If the input string is not the valid time. It will split the error.
Msg 289, Level 16, State 2, Line 51
Cannot construct data type time, some of the arguments have values which are not valid.
--- TIMEFORMPARTS()
SELECT TIMEFROMPARTS ( 23, 61, 59, 0, 0 ) AS Result
GO
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result
GO
EOMONTH()
EOMONTH() used to find the last day for the month .
--- EOMONTH() : last day for the month
select EOMONTH(GETDATE());
PARSE()
PARESE() can use to parse the time and currency.
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
GO
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'zh-TW') AS Result
GO
SELECT PARSE('€34,598' AS money USING 'de-DE') AS Result;
GO
SELECT PARSE('34,598' AS money USING 'zh-TW') AS Result;
0 comments:
Post a Comment