Tag Archives: New Feature in Sql Server 2012

DATEFROMPARTS FUNCTION IN SQL SERVER 2012

DATEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. As name suggests it forms a DATE from it’s parts i.e. Year, Month and Day. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATEFROMPARTS ( year, month, day )

WHERE: year, month and day are integer expressions representing valid year, month and day respectively.
Return Type: DATE

Example 1: DATEFROMPARTS – with valid date part integer constants

SELECT DATEFROMPARTS( 2013, 6, 23 ) AS 'DATE',
	DATEFROMPARTS( 1, 1, 1 ) AS 'MIN DATE',
	DATEFROMPARTS( 9999, 12, 31 ) AS 'MAX DATE'

Result:
DATE                    MIN DATE                 MAX DATE
———-                ———-                       ———-
2013-06-23       0001-01-01              9999-12-31

Example 2: DATEFROMPARTS – with valid date part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
2013-06-23

Example 3: DATEFROMPARTS – with invalid month part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23
SELECT DATEFROMPARTS( @year, @invalidmonth, @day) AS 'DATE'

Result:
Msg 289, Level 16, State 1, Line 2
Cannot construct data type date, some of the arguments have values which are not valid.

Example 4: DATEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = NULL
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

FORMAT STRING FUNCTION IN SQL SERVER 2012

FORMAT is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not an Sql Server native function instead it is .NET CLR dependent function.

SYNTAX: FORMAT ( value, format [, culture ] )

Parameter Description
value: Value to be formatted
format: This parameter specifies the format in which the vlaue will be formatted.
culture: This parameter is optional, it specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.

RETURNS: Return value type is nvarchar.

Example 1: FORMAT DATE with Culture

DECLARE @date DATETIME = GETDATE() 
SELECT @date AS 'GETDATE()',
       FORMAT( @date, 'd', 'en-US') AS 'DATE IN US Culture',
       FORMAT( @date, 'd', 'en-IN') AS 'DATE IN INDIAN Culture',
       FORMAT( @date, 'd', 'de-DE') AS 'DATE IN GERMAN Culture'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_1

Example 2: FORMAT CURRENCY with Culture

DECLARE @Price INT = 40
SELECT FORMAT(@Price,'c','en-US') 
         AS 'CURRENCY IN US Culture',       
    FORMAT(@Price,'c','de-DE')
         AS 'CURRENCY IN GERMAN Culture'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_2

Example 3: FORMAT CURRENCY

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT FORMAT( @Price, 'C') AS 'Default',
      FORMAT( @Price, 'C0') AS 'With 0 Decimal',
       FORMAT( @Price, 'C1') AS 'With 1 Decimal',
       FORMAT( @Price, 'C2') AS 'With 2 Decimal',
       FORMAT( @Price, 'C3') AS 'With 3 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_3

Example 4: FORMAT PERCENTAGE

DECLARE @Percentage float = 0.35674
SELECT FORMAT( @Percentage, 'P') AS '% Default',
       FORMAT( @Percentage, 'P0') AS '% With 0 Decimal',
       FORMAT( @Percentage, 'P1') AS '% with 1 Decimal',
       FORMAT( @Percentage, 'P2') AS '% with 2 Decimal',
       FORMAT( @Percentage, 'P3') AS '% with 3 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_4

Example 5: FORMAT NUMBER

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT FORMAT( @Number, 'N','en-US') AS 'Number Format in US',
    FORMAT( @Number, 'N','en-IN')  AS 'Number Format in INDIA'

SELECT FORMAT( @Number, '#.0')     AS 'With 1 Decimal',
    FORMAT( @Number, '#.00')    AS 'With 2 Decimal',
    FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal',
    FORMAT( @Number, '##.00')   AS 'Without Comma and 2 Decimal'

Result:
FORMAT_FUNCTION_IN_SQL_SERVER_2012_5

Example 6: CUSTOM DATE FORMATS

DECLARE @date DATETIME = GETDATE() 
SELECT @date AS 'GETDATE()',
    FORMAT ( @date, 'dd/MM/yyyy') AS 'dd/MM/yyyy',
    FORMAT ( @date, 'MM/dd/yyyy') AS 'MM/dd/yyyy',
    FORMAT ( @date, 'yyyy/MM/dd') AS 'yyyy/MM/dd' 

SELECT 
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US')
   AS 'US',
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN')
   AS 'Hindi',
 FORMAT( @date,'dddd, MMMM dd, yyyy hh:mm:ss tt','kn-IN')
  AS 'Kannada'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_6

DECLARE @date DATETIME = GETDATE()
SELECT FORMAT ( @date, 'dd', 'en-US' ) AS 'US',
	FORMAT ( @date, 'ddd', 'en-US' )   AS 'US',
	FORMAT ( @date, 'dddd', 'en-US' )  AS 'US',
	FORMAT ( @date, 'dddd', 'kn-IN' )  AS 'Kannada',
	FORMAT ( @date, 'dddd', 'hi-IN' )  AS 'Hindi'

SELECT FORMAT ( @date, 'M', 'en-US' )  AS 'US',
	FORMAT ( @date, 'MM', 'en-US' )    AS 'US',
	FORMAT ( @date, 'MMM', 'en-US' )   AS 'US',
	FORMAT ( @date, 'MMMM', 'en-US' )  AS 'US',
	FORMAT ( @date, 'MMMM', 'kn-IN' )  AS 'Kannada',
	FORMAT ( @date, 'MMMM', 'hi-IN' )  AS 'Hindi'

SELECT FORMAT ( @date, 'y', 'en-US' )   AS 'US',
       FORMAT ( @date, 'y', 'kn-IN' )   AS 'Kannada',
       FORMAT ( @date, 'y', 'hi-IN' )   AS 'Hindi',
       FORMAT ( @date, 'yy', 'en-US' )  AS 'US',
       FORMAT ( @date, 'yyy', 'en-US' ) AS 'US'

Result:

FORMAT_FUNCTION_IN_SQL_SERVER_2012_7

Example 7: Invalid Culture

DECLARE @date DATETIME = GETDATE()
SELECT FORMAT(@date,'d','Test') AS 'Invalid Culture'

Result:
Msg 9818, Level 16, State 1, Line 2
The culture parameter ‘Test’ provided in the function call is not supported.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

EOMONTH FUNCTION IN SQL SERVER 2012

EOMONTH is one of the new built-in Date and Time function introduced as a Part of Sql Server 2012. It returns the last date of the Month that contains the specified date in it. It is a Sql Server native function, not dependent on the .NET CLR.

SYNTAX: EOMONTH ( start_date [, month_to_add ] )

Parameters:
start_date: Date Expression representing the date, for which the last date of the Month is returned
month_to_add: This is an optional integer parameter, if it is specified then specified number of months is added to the start_date and then last date of the month for the resultant date is returned.

Return Type: Date

Example 1: EOMONTH – start_date as DATETIME type

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 2: Last date of the month in previous versions of Sql Server like Sql Server 2008, 2005, 2000.

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(MONTH,datediff(MONTH,-1, @date),-1) EOFMONTH

Result:
EOFMONTH
———————–
2012-06-30 00:00:00.000

Example 3: EOMONTH – Start_date as valid date expression of type VARCHAR

DECLARE @date AS varchar(10)= '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 4: Current/Previous/Previou-to-Previous month’s lastdate using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, -1) 'Previous Month',
                EOMONTH(@date, -2) 'Previous-to-Previous Month'

Result:
Current Month Previous Month Previous-to-Previous Month
————- ————– ————————–
2012-06-30 2012-05-31 2012-04-30

Example 5: Current/Next/Next-To-Next month’s last date using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, 1) 'Next Month',
                EOMONTH(@date, 2) 'Next-to-Next Month'

Result:
Current Month Next Month Next-to-Next Month
————- ———- ——————
2012-06-30 2012-07-31 2012-08-31

Example 6: Start Date of the Month using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) 
                AS 'Current Month Start Date'

Result:
Current Month Start Date
————————
2012-06-01

Example 7: Invalid start_date

SELECT EOMONTH('2012/02/31') EOFMONTH

Result:
EOFMONTH
———-
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

CHOOSE LOGICAL FUNCTION IN SQL SERVER 2012

CHOOSE is one of the new built-in logical function introduced as a Part of Sql Server 2012. It returns the value at the specified index position from the list of values.

SYNTAX: CHOOSE ( index, val_1, val_2 [, val_n ] )

Example 1: CHOOSE Basic Example

SELECT CHOOSE(1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(2,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(4,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_1

Example 2: CHOOSE function with variables as index and values.

DECLARE @SeasonId INT = 2, @Season1 Varchar(10) = 'Spring',
 @Season2 Varchar(10) = 'Summer',@Season3 Varchar(10) = 'Autumn',
 @Season4 Varchar(10) = 'Winter'

SELECT CHOOSE(@SeasonId, @Season1, @Season2, @Season3, @Season4)

Result: Summer

Example 3: CHOOSE will return NULL if INDEX position is outside the range of values.

SELECT CHOOSE(-10,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(0,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(5,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_2

Example 4: If index value is numeric, it will be implicitly converted to INT.

SELECT CHOOSE(3.1,'Spring','Summer','Autumn','Winter')
SELECT CHOOSE(3.85,'Spring','Summer','Autumn','Winter')

Result:
CHOOSE_LOGICAL_FUNCTION_SQL_SERVER_2012_3

Example 5: CHOOSE function Return Datatype will be the one with highest precedence from the Datatypes of the list of values

SELECT CHOOSE(3, 40.58, 50, 60, 70)

Result: 60.00

Example 6: CHOOSE function with Index Type as Varchar

SELECT CHOOSE('TEST','Spring','Summer','Autumn','Winter')

Result:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘TEST’ to data type int.

SELECT CHOOSE('3','Spring','Summer','Autumn','Winter')

Result: Autumn

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

Please correct me, if my understanding is wrong. Comments are always welcome.

IIF() LOGICAL FUNCTION IN SQL SERVER 2012

IIF() is one of the new built-in logical function introduced as a Part of Sql Server 2012. IIF() is the shorthand way of writing CASE statement or IF-ELSE statement. In-fact if we see the execution plan the IIF() function internally translates to a CASE statement.

IIF() function takes three arguments, first argument should be a Boolean expression otherwise it raises an exception. If Boolean expression evaluates to TRUE then Second argument will be the result otherwise Third argument will be the result. This functions result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

Syntax: IIF ( boolean_expression, true_value, false_value )

Now let us understand this function with examples:

Example 1: Comparing two Integer numbers

SELECT IIF(1 > 10, 'TRUE', 'FALSE' )

Result: FALSE

Example 2: Comparing two Integer Variables

DECLARE @Marks INT = 60, @Minimum INT = 35
SELECT IIF(@Marks >= @Minimum, 'PASS', 'FAIL' )

Result: PASS

Example 3: Comparing Strings

DECLARE @NAME Varchar(50) = 'Basav'
SELECT IIF(@NAME IN ('Basav','Kalpana'), 
                    'Likes SQL', 'Likes ORACLE' )

Result: Likes SQL

Example 4: Result’s Datatype will be the one with highest precedence from the Datatypes of Second and Third Argument.

SELECT IIF(1 > 10, 1.5, 40)

Result: 40.0

Example 5: Nested IIF() Statement.

DECLARE @Percentage AS NUMERIC(5,2) = 71
SELECT IIF(@Percentage >= 70, 'Distinction', 
        IIF(@Percentage>=35 AND @Percentage<70, 'Pass', 'Fail'))

Result: Distinction

Note: As IIF() statement internally translates to an CASE statement, so the Max Nesting level is 10.

Example 6: IIF() with both return value arguments value as NULL constant

SELECT IIF(1 > 2, NULL, NULL)

Result:
Msg 8133, Level 16, State 1, Line 1
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant

Example 7: IIF() with on of the return value argument value as NULL constant

SELECT IIF(1 > 2, NULL, 'NO')

Result: NO

Example 8: IIF() with both return value arguments are variables with value NULL

DECLARE @A INT = NULL, @B INT = NULL
SELECT IIF(1 > 2, @A, @B)

Result: NULL

You may also like to read other LOGICAL FUNCTION CHOOSE() introduced in SQL SERVER 2012

Please correct me, if my understanding is wrong. Comments are always welcome.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS