New Date,Logical Functions in SQL Server 2012

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.

For example:

--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)

Result

2011-12-11 00:00:00.000

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;

Result

2011-11-12

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;

Result:

2011-11-12 11:34:16.500

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.

Result:

23:59:59.50

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.

Example 1:

--End of Month
DECLARE @date DATETIME
SET @date = '11/12/2010'
SELECT EOMONTH ( @date ) AS Result;

Result:

2010-11-30

Example 2:

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;

Result:

2010-09-30

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.

Example:

DECLARE @a int = 55;
DECLARE @b int = 40;
SELECT CASE when @a > @b then 'TRUE' else  'FALSE' END AS Result;

Result

TRUE

or

DECLARE @a int = 55;
DECLARE @b int = 40;
IF @a > @b 
Begin
select 'TRUE' as Result
end
else
begin
select 'FALSE' as Result
end

Result

TRUE

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;

Result

TRUE

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

Example:

SELECT CHOOSE ( 3, 'Dad', 'Mom', 'Son', 'Daughter' ) AS Result;

Result:

Son

Conclusion:

I have illustrated few of the new DATE and Logical functions in this article. In an upcoming article I will cover more of the new SQL Server 2012 and its features in detail.

See all articles by MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles