As we all know, Microsoft officially released SQL Server 2012 RC0. You can register and download the binaries from http://www.microsoft.com/sql.
As with every new release, Microsoft added more features and enhancements, improvements not only to the SQL Server database engine but also to other features in SQL Server.
Some of the improvements that Microsoft did are on Transaction SQL.
In this article, I am going to illustrate some of the new functions that are introduced in SQL Server 2012. They are related to the DATE and LOGICAL functions.
The first function I want to discuss is related to the date function as it relates to concatenating different numerical date parts and changes the data type to date time. Let us run the following example code.
--Date from DaTE PARTS declare @year int declare @month int declare @day int set @year =2011 set @month=11 set @day =12 SELECT Date=Convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@month)+'-'+convert(varchar(10),@day),103)
In the above example, you notice that we are converting three integers to varchar, concatenating with hyphen and then converting the entire string to a date time format. Microsoft made this simple by introducing a new function called DATEFROMPARTS.
Let’s us use the above variables to the new function shown below.
declare @year int declare @month int declare @day int set @year =2011 set @month=11 set @day =12 SELECT DATEFROMPARTS ( @year, @month, @day ) AS Result;
The second date function I would like to discuss is similar to above function. However it has the time parts and precision added to it.
--Date time from date time parts declare @year int declare @month int declare @day int declare @hr int declare @min int declare @sec int set @year =2011 set @month=11 set @day =12 set @hr =11 set @min =34 set @sec =16 SELECT DATETIME2FROMPARTS ( @year, @month, @day, @hr, @min, @sec,500,3 ) AS Result;
Note: When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.
The third date function I would like to discuss is using time parts to generate time date type. The following example is self-explanatory.
declare @hr int declare @min int declare @sec int set @hr =11 set @min =34 set @sec =16 SELECT TIMEFROMPARTS ( 23, 59, 59, 50, 2 )AS Result;
Note: When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
The next interesting date function I’d like to discuss is the End of month function. We used to create our own function to generate END of month calculation before. Now Microsoft has introduced the EOMONTH function
In the example below, you can see that EOMONTH function calculated the date related to the end of month based on the date passed as a parameter.
--End of Month DECLARE @date DATETIME SET @date = '11/12/2010' SELECT EOMONTH ( @date ) AS Result;
In the example below, you can see that you can also pass month_to_add parameter.
Here I am passing a negative value.
DECLARE @date DATETIME SET @date = '11/12/2010' SELECT EOMONTH ( @date,-2 ) AS Result;
The next new function is the logical function IIF. DBAs and developers who worked on MS-Access are familiar with this function.
Prior to SQL Server 2012, if you want to return true or false based on a particular condition or choose a value based on a logical condition, you had to use the IF condition or CASE statement.
DECLARE @a int = 55; DECLARE @b int = 40; SELECT CASE when @a > @b then 'TRUE' else 'FALSE' END AS Result;
DECLARE @a int = 55; DECLARE @b int = 40; IF @a > @b Begin select 'TRUE' as Result end else begin select 'FALSE' as Result end
We don’t have to do this anymore. Instead we can use a simple IIF.
DECLARE @a int = 55; DECLARE @b int = 40; SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
Another new logical function is the CHOOSE function. If you want to choose one from a bunch of values, this will be a great function
SELECT CHOOSE ( 3, 'Dad', 'Mom', 'Son', 'Daughter' ) AS Result;