New T-SQL Functions in SQL Server 2012

Getting Start and End of the Month made easy in 2012:

--End of the Month

SELECT EOMONTH(GETDATE())

--Start of the Month

SELECT DATEADD(D,1,EOMONTH(GETDATE(),-1))

TRY_PARSE and PARSE Functions:

TRY_PARSE returns the result of the expression, translated to the requested data type, or null if the cast fails.

Detecting nulls with TRY_PARSE:

Select TRY_PARSE ('01/01/1900' as Int) As Result;

While PARSE returns the result of the expression, translated to the requested data type.

SELECT PARSE('Monday, 13 December 2010' AS datetime) AS Result;

Using IIF with TRY_PARSE:

Select IIF(TRY_PARSE ('01/01/1900' as Int) IS NULL,1,0) As Result;

Functions SOME, ANY and ALL:

Compares a scalar value with a single-column set of values. SOME and ANY are equivalent.

The following statements create a simple table and add the values of 10, 20, 30, 40 and 50 to the ID column:

CREATE TABLE #TESTFn

(TESTID int) ;

GO

INSERT #TESTFn VALUES (10) ;

INSERT #TESTFn VALUES (20) ;

INSERT #TESTFn VALUES (30) ;

INSERT #TESTFn VALUES (40) ;

INSERT #TESTFn VALUES (40) ;

The following query returns TRUE because 25 is less than some of the values in the table:

IF 25 < SOME (SELECT TESTID FROM #TESTFn)

PRINT 'TRUE'

ELSE

PRINT 'FALSE' ;

The following query returns FALSE because 25 is not less than all of the values in the table.

IF 25 < ALL (SELECT TESTID FROM #TESTFn)

PRINT 'TRUE'

ELSE

PRINT 'FALSE' ;