5 New T-SQL Functions in SQL 2012

• 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;

Reference

• FORMAT:http://msdn.microsoft.com/en-us/library/hh213505.aspx
• PARSE: http://msdn.microsoft.com/en-us/library/hh213316.aspx