New and Enhanced tSQL Functions in SQL Server 2012

Enhanced and New  tSQL Functions in SQL Server 2012

1. Try_Convert 
/*Try_Convert and Try_Parse Functions to handle errors by returning NULL values*/

SELECT TRY_CONVERT(INT, 100) AS CorrrectConvert, TRY_CONVERT(INT, 'abc') AS ErrorConvert;

Result:

CorrrectConvert	ErrorConvert
100	NULL

SELECT PARSE('7/17/2011' AS DATE USING 'en-US') AS USDate, PARSE('2011/7/17' AS DATE USING 'ja-JP') AS JapanDate;
Result:

USDate	JapanDate
7/17/2011	7/17/2011

2. TRY_PARSE
/*If we try to parse Invalid values it will show below error like japan support 2011 but if we pass only 11
it will show error*/
SELECT PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;

Error:
--Msg 9819, Level 16, State 1, Line 1
-- Error converting string value '7/17/11' into data type date using culture 'ja-JP'.

SELECT TRY_PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt;



3. Date and Time Functions
SELECT
DATEFROMPARTS(2014, 07, 22)    AS DATE_FROMPARTS,
DATETIME2FROMPARTS(2014, 07, 22, 14, 31, 7, 2, 3) AS DATETIME2_FROMPARTS,
DATETIMEFROMPARTS(2014, 07, 22, 14, 31, 7, 997) AS DATETIME_FROMPARTS,
DATETIMEOFFSETFROMPARTS(2014, 07, 22, 14, 31, 5, 1, -5, 0, 7) AS DATETIMEOFFSET_FROMPARTS,
SMALLDATETIMEFROMPARTS(2014, 07, 22, 14, 31) AS SMALLDATETIME_FROMPARTS,
TIMEFROMPARTS(15, 33, 6, 1, 7) AS TIME_FROMPARTS;



4. Logical/Conditional Functions
SELECT CHOOSE(3, 43466, 'Delhi', 'Agra') AS '1stPosition', CHOOSE(5, 'MUM', 'pune', 'Bangalore','allahabad','KNP') AS '5thPosition',CHOOSE(3,'a','b') 'NoPosition' ;

SELECT IIF(4 = 5, 'Ram', 'Shyam') AS iif_result;
SELECT IIF(4 = 4, 'Ram', 'Shyam') AS iif_result;




5. Concate and Format
DECLARE   @Passenger TABLE
  (ID INT,
  PassengerID VARCHAR(10),
       CityName   VARCHAR(10),
       StartDate  DATE
    )

INSERT INTO @Passenger VALUES (1,'P00001', 'Delhi', '1-Sep-2014')
INSERT INTO @Passenger VALUES (2,'P00001', 'Mathura', '1-Oct-2014')
INSERT INTO @Passenger VALUES (3,'P00001', 'Etah', '10-Oct-2014')

Query1.
SELECT *,CONCAT(PassengerID, ', ' + 'Name&Type_Desc', ', ' + CityName) AS ConcatinatedInfo
FROM  @Passenger
Result:

ID	PassengerID	CityName	StartDate	ConcatinatedInfo
1	P00001	Delhi	9/1/2014	P00001, PsgrID&CtyName, Delhi
2	P00001	Mathura	10/1/2014	P00001, PsgrID&CtyName, Mathura
3	P00001	Etah	10/10/2014	P00001, PsgrID&CtyName, Etah

Query2.

SELECT *,CONCAT(PassengerID, ', ' + 'PsgrID&CtyName', ', ' + CityName) AS ConcatinatedInfo
FROM  @Passenger

Result:

USDateFormat	FormatWithZero
12/11/2014	00000001
12/11/2014	00000002
12/11/2014	00000003

Query3.
SELECT FORMAT(GETDATE(), 'd', 'en-us') AS USCulture, FORMAT(GETDATE(), 'd', 'ja-JP') AS JAPANECULTURE,FORMAT(GETDATE(), 'd', 'de-DE') AS GERMANCULTURE

Result:

USCulture	JAPANECULTURE	GERMANCULTURE
12/11/2014	12/11/2014	11.12.2014

6. LOG
Query4.
SELECT LOG(256, 2);
Result.

LOG

8

7. EOMONTH
Query5.
SELECT EOMONTH('12/15/2014')Endofmonth ,EOMONTH(SYSDATETIME()) AS Endofmonth1,EOMONTH(getdate()) AS endofmonth2;

Result:

Endofmonth	Endofmonth1	endofmonth2
12/31/2014	12/31/2014	12/31/2014