Tag Archives: New Analytical functions in SQL Server 2012

Using DATEFROMPARTS() – SQL Server 2012

DATEFROMPARTS() returns a date value for the specified year, month, and day.

Let us understand this with an example.

The SQL code for returning day, month and year parameters as a date would probably look something like this

DECLARE @Day INT = 07, @Month int = 03,@Year INT = 2014
SELECT CONVERT(datetime,CONVERT(varchar(10),@Year) + '-' +
CONVERT(varchar(10),@Month) + '-' +
CONVERT(varchar(10),@Day),103) AS TheDate

datetime1Now lets implement DATEFROMPARTS

Here is the code for the same

DECLARE @Day INT = 07,
@Month int = 03,
@Year INT = 2014
SELECT DATEFROMPARTS (@Year, @Month, @Day) AS TheDate

datetime2

CHOOSE – SQL Server 2012

In this article we will understand this new logical function named CHOOSE in SQL Server 2012.

So here is how the syntax of CHOOSE looks like

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

Now let us use this in the below code and understand the output.

declare @MyFavouriteClub as int = 5
SELECT choose(@MyFavouriteClub, 'Arsenal', 'Chelsea',
'Liverpool', 'Everton', 'Manchester United',
'Sunderland', 'Fulham')

Now lets see the output of the above
choose1So what the function does is pulls out the 5th value from the data set and dispalys as a result set. CHOOSE works like an index into an array, where the array contains values or arguments. The index value\argument determines which of the containing values will be returned. If no match is found then a NULL is returned.

%d bloggers like this: