Tuesday, September 30, 2014

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

image

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;

image

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;

image

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

image

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

image

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

image

image

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.

image

--- TIMEFORMPARTS()
SELECT TIMEFROMPARTS ( 23, 61, 59, 0, 0 ) AS Result
GO
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result
GO

image

EOMONTH()

EOMONTH()  used to find the last day for the month .

--- EOMONTH() : last day for the month 
select EOMONTH(GETDATE());

image

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;

image

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates