SQL Server 2012 introduced a new range of features all aimed at improving delivery of Business Intelligence (BI) solutions. This included some improvements to T-SQL which could easily be overlooked, but are extremely useful, particularly in the development of BI solutions.

Immediate If

A long term frustration of many T-SQL developers has been the absence of an Immediate If function, having instead to use a lengthy IF-THEN-ELSE or CASE WHEN statement to perform the same task. This function returns one of two values, depending on the result of a Boolean expression.

IIF ( Boolean_expression, true_value, false_value )

Try Convert

Converting data from one type to another is a common task, especially where multiple data sources are being used to obtain staging data. If a staging table for example, contains data which has been loaded from a text file and now needs to be loaded into an Enterprise table with its correct data type, any bad data may cause the process to fail. Where it’s not necessary to capture any rows containing invalid data for further analysis, try_convert is useful because if it can’t return a value cast to the specified data type, it returns a null instead.

TRY_CONVERT ( data_type [ (length) ], expression [, style ] )

End of Month

Another frustration with T-SQL has always been the number of steps required to find the last day of the month. The EOMONTH function makes this extremely easy and also contains an option to offset the date by a specified number of months.

EOMONTH ( start_date [, month_to_add ] )

If the month_to_add option is used, the function will add that number of months to the start_date and return the last day of the resulting month.

Date from Parts

The Date From Parts function makes is much simpler to rebuild a date value from its constituent parts. Simply taking year, month and day values, the function returns a correctly formatted date.

DATEFROMPARTS ( year, month, day )

Back to blog