Feeds:
Posts
Comments

Archive for August, 2012

SQL SERVER 2012 -Date and Time Function – DATEFROMPARTS()

Posted in Functions, SQL SERVER, tagged , , on August 31, 2012| 3 Comments »

Before SQL SERVER 2012, If we have a scenario that Year, Month & day are given to us and we need to convert it into date and return it. In this case we need to do a lot of conversion and then we can get our desired results.

For Example :

--SQL Server 2008
Declare @Year as int=2012
Declare @Month as int=12
Declare @Day as int=31

Select Convert(Date,Convert(varchar(4), @Year)
+
Convert(varchar(2), @Month)
+
Convert(varchar(2), @Day))
--Result
2012-12-31

But In SQL Server 2012, the solution came as a new built in function namely DATEFROMPARTS().
Lets discuss this function syntax, parameters, purpose and examples in detail.

Syntax :

DATEFROMPARTS ( year, month, day )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31 (depends upon the total number of days in a month)

Purpose :
This function requires a valid Year, Month & day as a parameter and returns  a valid date. If we pass any invalid date part to this function it will return an error. Also if we pass NULL to any of its parameter, it returns NULL value.

Let me explain this with simple examples.

Example-1 : DATEFROMPARTS – With valid date parts

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-2 : DATEFROMPARTS – With Invalid date parts
If we provide an invalid date part to this function, it will generate an error because only valid date parts are allowed as a parameter.

Declare @Year as int=2012
Declare @Month as int = 13 -- 13 is not a Valid month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

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

Example-3 : DATEFROMPARTS – With NULL Date Parts
If we pass NULL value to any parameter of this function, It will return NULL value.

Declare @Year as int=2012
Declare @Month as int = NULL -- Passed NULL as a Month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-4 : DATEFROMPARTS – With missing parameters
As I have mentioned above that all parameters(3) are mandatory for DATEFROMPARTS function. Lets try to pass less than 3 parameters.

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month) as [Result]
--Result

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Analytic Function – LAST_VALUE

Posted in Functions, SQL SERVER, tagged , , , on August 30, 2012| Leave a Comment »

In my previous Post, I discussed about FIRST_VALUE.Today, we will discuss another important analytical function namely “LAST_VALUE”. We can also say this is the reverse of FIRST_VALUE analytical function.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

Purpose :
It returns the Last value from a list in a specified order. Also we can define partition & rows range parameter in this function, but these are optional parameters. By default Row range is between unbounded preceding and current row or range unbounded preceding.
It is better to define “row range” parameter in the LAST_Value function. Later in this article, we will also discuss the importance of “row range” parameter in LAST_VALUE.

Lets create a student table and insert few records in it to demonstrate LAST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)

Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)

Select * from [Student] Order By [Student ID]

Example-1 :
In this example, LAST_VALUE returns the Last Name of the student in the class (In alphabetical order) . And the expected result is “Sandra”.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Student Name])
as [Last Value]
from dbo.[Student]
--Result

But Unfortunately, this is not our expected result, It is the same row value in Last Value &  student name column.

Here “row range ” optional parameters of LAST_VALUE comes to solve this problem. Now, we need to change the “row range” value from default to CURRENT ROW AND UNBOUNDED FOLLOWING.

Lets make another example with row range CURRENT ROW AND UNBOUNDED FOLLOWING.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Student Name]
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) as [Last Value]
from dbo.[Student]
--Result

Now, we got our expected result by defining “row range”.

Example-2 :
In this example, LAST_VALUE returns the student name having least score in the class. Expected result is : “Sandra”. Because she is having least score (40) in science in the entire class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Order By [Marks] Desc
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS [LAST Value]
from dbo.[Student]
--Result

Example-3 :
In this example, LAST_VALUE returns the student name having least score in each subject in the class. Expected result is : Sandra in “English & Science” & Derek in “Maths”.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,LAST_Value([Student Name]) Over (Partition By [Subject]
Order By [Marks] Desc
ROWS BETWEEN  CURRENT ROW AND UNBOUNDED FOLLOWING) AS [LAST Value]
from dbo.[Student]
--Result

Reference : MSDN

Read Full Post »

SQL SERVER 2012 – Analytic Function – FIRST_VALUE

Posted in Functions, SQL SERVER, tagged , , on August 29, 2012| 5 Comments »

In SQL server 2012, Microsoft has introduced some new analytical functions and one of the most important functions in it is FIRST_VALUE. As It name implies, it does the same.

Lets discuss this function syntax, purpose and examples in detail.

Syntax :

FIRST_VALUE ( [scalar_expression )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

Purpose :
It returns the first value from a list in a specified order (By default ascending order). Also we can do the partition in it but it is optional.

Lets create a student table and insert few records in it to demonstrate FIRST_VALUE.

Create Table [Student]
(
[Student ID] int,
[Student Name] varchar(50),
[Subject] varchar(50),
[Marks] int
)
GO

Insert into [Student] values (3,'Derek','English',100)
Insert into [Student] values (3,'Derek','Math',60)
Insert into [Student] values (3,'Derek','Science',90)

Insert into [Student] values (1,'Bob','English',80)
Insert into [Student] values (1,'Bob','Math',75)
Insert into [Student] values (1,'Bob','Science',60)

Insert into [Student] values (2,'Sandra','English',70)
Insert into [Student] values (2,'Sandra','Math',80)
Insert into [Student] values (2,'Sandra','Science',40)

Select * from [Student] Order By [Student ID]

Example-1 :
In this example, FIRST_VALUE returns the First Name of the student in the class (In ascending order) .

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Student Name]) as [First Value]
from dbo.[Student]
--Result

Example-2 :
In this example, FIRST_VALUE returns the student name having high score in the class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Example-3 :
In this example, FIRST_VALUE returns the student name having high score in each subject in the class.

Select
[Student ID]
,[Student Name]
,[Subject]
,[Marks]
,FIRST_Value([Student Name]) Over (Partition By [Subject] Order By [Marks] Desc) as [First Value]
from dbo.[Student]
--Result

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CAST

Posted in Functions, SQL SERVER, tagged , , , on August 28, 2012| Leave a Comment »

In my previous post, I discussed about TRY_CONVERT. Today, we will discuss one more excellent conversion function of SQL server namely “TRY_CAST”. Before SQL server 2012, we use “Cast” function very frequently. whenever we need to cast expression from one data type to another data type, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to cast it into incompatible data type. It will generate an error. TRY_CAST is the solution of this problem.
For Example :

Select Cast ('test' as int)
--Result

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

TRY_CAST requires three parameters expression, data type & Length. Expression and Data type are mandatory but the Length is optional.
Lets discuss this function syntax, purpose and examples in detail.

Syntax :

TRY_CAST ( expression AS data_type [ ( length ) ] )

Purpose :
This function cast expression from one data type to another data type and if it fails to cast, it give result as NULL.

Example-1 : TRY_CAST – Without Error

Select TRY_Cast(123 as int) as [Result]
--Result
123

Example-2 : TRY_CAST – With Error
If TRY_CAST fails, it will return NULL value.

Select TRY_Cast ('test' as int) as [Result]
--Result
NULL

Example-3 : TRY_Cast WITH IIF STATEMENT

In this example , we will try to convert a valid and invalid expression to Integer by using IIF statement.

Select IIF(Try_Cast('test' as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Invalid Integer

Select IIF(Try_Cast(2 as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Valid Integer

Example-4 : TRY_Cast WITH CASE STATEMENT
In this example , we will try to cast a valid and invalid expression to Integer by using case statement.

Select (Case When Try_Cast(123 as int  ) is NULL Then 'Invalid Integer' else 'Valid Integer' end)
--Result
Valid Integer

Select (Case When Try_Cast(123.45 as int) is NULL Then 'Invalid Integer' else  'Valid Integer' end)
--Result
Invalid Integer

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CONVERT

Posted in Functions, SQL SERVER, tagged , , , , on August 27, 2012| 7 Comments »

In SQL server, we use “Convert” function very frequently.Whenever, we need to convert one expression data type to another, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to convert it. It will generate an error and can impact you application.
For Example :

Select Convert (datetime2,'2012-08-32')
--Result

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

To resolve this issue, Microsoft has introduced a new function in SQL SERVER 2012, namely “TRY_CONVERT”.
It requires three parameters data type, expression & style. Data type and expression are mandatory but the style is optional.
Lets discuss this function syntax , purpose and examples in detail.

TRY_CONVERT

Syntax :

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

Purpose :
This function converts expression from one data type to another data type and if it fails to convert it will return a NULL value as a results.

Example-1 : TRY_CONVERT – Passed

Select TRY_Convert(datetime,'2012-09-30',101) as [Result]
--Result
2012-09-30 00:00:00.000

Example-2 : TRY_CONVERT – Failed
If try _convert Failed,  it will return NULL value.
Lets say we don’t have 31 days in September. Now, try to convert it

Select TRY_Convert(datetime,'2012-09-31',101) as [Result]
--Result
NULL

Example-3 : TRY_CONVERT WITH IIF STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select IIF(Try_Convert(int, 'test') is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Invalid Integer

Select IIF(Try_Convert(int, 2) is NULL , 'Invalid Integer', 'Valid Integer') as [Result]
--Result
Valid Integer

Example-4 : TRY_CONVERT WITH CASE STATEMENT
In this example , we will try to convert a valid and invalid expression to Integer.

Select (Case When Try_Convert(datetime2,'2012-08-22') is NULL Then 'Invalid Date format' else 'Valid Date format' end) as [Result]
--Result
Valid Date format

Select (Case When Try_Convert(datetime2,'2012-08-32') is NULL Then 'Invalid Date format' else  'Valid Date format' end) as [Result]
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_PARSE()

Posted in Functions, SQL SERVER, tagged , , on August 27, 2012| 1 Comment »

In my previous post, I discussed about PARSE function. In this function, if the data type is not compatible and could not be parsed then this function will generate an error. Now, we have the solution for it namely “TRY_PARSE”. SQL SERVER has introduced this (TRY_PARSE) built in conversion function in 2012. This function is very useful because most of the time due to the incorrect data , your system may generate errors. And if you use this function it will return NULL in case of error.
Lets discuss its syntax , purpose and examples in detail.

TRY_PARSE

Syntax :

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function tries to parse string value (nvarchar(4000) data type) to datetime or numeric data types. But if it fails to parse, it will return NULL value. We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : TRY_PARSE – Successful 

SELECT TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US')
--Result
2012-08-22 00:00:00.0000000

Example-2 : TRY_PARSE – Unsuccessful
If the parsing will be unsuccessful, it will return NULL value.

SELECT TRY_PARSE('datetime2' AS datetime2 USING 'en-US')
--Result
NULL

Example-3 : TRY_PARSE – Real World Example with culture keyword
In this example, we will try to parse a valid and invalid date.

Select IIF(TRY_PARSE('2012-08-22' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2 USING 'en-US') is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Example-4 : TRY_PARSE – Real World Example with Language keyword
In this example, we will try to parse a valid and invalid date with the language keyword instead of culture.

Set Language 'English'
Select IIF(TRY_PARSE('2012-08-22' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Valid Date format

Select IIF(TRY_PARSE('2012-08-222' AS datetime2) is NULL
,'Invalid Date format' ,'Valid Date format')
--Result
Invalid Date format

Reference : MSDN

Read Full Post »

SQL SERVER 2012 -Conversion Function – PARSE ()

Posted in Functions, SQL SERVER, tagged , , on August 26, 2012| 2 Comments »

SQL SERVER 2012 has a new built in function namely “PARSE”. Lets discuss its syntax , purpose and examples in detail.

PARSE

Syntax :

PARSE ( string_value AS data_type [ USING culture ] )

Purpose :
This function converts the string value (nvarchar(4000) data type) to datetime or numeric data types.
We need to provide three parameters to this function string value , data type & culture parameter respectively. String value & data type parameter are mandatory but the culture parameter is optional . As the culture parameter is option so, by default it picks up the culture from the session.It uses the .NET Framework Common Language Run time(CLR). Before using this function, we should also know, that it cost extra overhead in the performance.

Example-1 : PARSE -Varchar to Numeric

SELECT PARSE('123.45' AS NUMERIC(18,2)) AS Output
--Result
123.45

Example-2 : PARSE – Varchar to Int
Lets use the same example of numeric and cast it to integer.

SELECT PARSE('123.45' AS INT) AS Output
--Result

Msg 9819, Level 16, State 1, Line 1
Error converting string value ‘123.45’ into data type int using culture .
Note : The above line of code will generate error because whatever we want to parse should be compatible with the parse data type.

Now, lets do the same without scale.

SELECT PARSE('123' AS INT) AS Output
--Result
123

Example-3 : PARSE – Varchar to Datetime with Default culture.

Select Parse('Aug 26 2012' as datetime)
Select Parse('08/26/2012' as datetime )
Select Parse('2012.08.26' as datetime)
Select Parse('26 Aug 2012' as datetime)
Select Parse('Aug 26, 2012' as datetime)
Select Parse('08-26-2012' as datetime)
Select Parse('2012/08/26' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Example-4 : PARSE – Varchar to Datetime with culture

Select Parse('26/08/2012' as datetime using 'fr-FR')  --French
Select Parse('26.08.2012' as datetime using 'de-DE')  --German
Select Parse('26-08-2012' as datetime using 'it-IT')  --Italian
--Result of the above statements
2012-08-26 00:00:00.000

Example-5 : PARSE – Varchar to Currency with culture

Select Parse('$123.45' as money using 'en-US')
Select Parse('€123,45' AS money using 'de-DE')
--Result of the above statements
123.45

Example-6 : PARSE – Language Setting Instead of Culture

SET LANGUAGE 'French'
Select Parse('26/08/2012' as datetime)

SET LANGUAGE 'German'
Select Parse('26.08.2012' AS datetime2) AS Result

SET LANGUAGE 'Italian'
Select Parse('26-08-2012' as datetime)
--Result of the above statements
2012-08-26 00:00:00.000

Error :
If the string value is invalid to convert it into date time, numeric & any specific culture format, then it will generate an error.

Reference : MSDN

Read Full Post »

SQL Server 2012 – New Logical Function – IIF

Posted in Functions, SQL SERVER, tagged on August 6, 2012| 6 Comments »

SQL Server 2012 introduced new logical function namely “IIF”. Lets discuss the syntax and purpose of this function in detail.

IIF:

Syntax :

IIF ( boolean_expression, true_value, false_value )

Purpose :
The purpose of this logical function is to check the Boolean condition and if the condition is true it will return true value otherwise false value.
This function is available in SSRS and now, it is available in SQL SERVER 2012. By using this function, you don’t need to use if else , you can validate and return value in a single line of code.

Examples :

Example-1

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, 'TRUE', 'FALSE' ) AS Result;
--Result
FALSE

Example 2

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, NULL ) AS Result;
--Result

Msg 8133, Level 16, State 1, Line 3</span>
At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
Reason : Because both the constant are NULL, even if one constant is not NULL it will not give this error as explained in example 3.
But, you can pass NULL parameter in both true and false value and SQL server will accept it as explained in example 4.
Example 3

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
SELECT IIF ( @int1> @int2, NULL, 'False' ) AS Result;
--Result
False

Example 4

DECLARE @int1 int = 100;
DECLARE @int2 int = 200;
DECLARE @Condition_True int = NULL;
DECLARE @Condition_False int = NULL;
SELECT IIF ( @int1> @int2, @Condition_True, @Condition_False) AS Result;
--Result
NULL

Example 5 :
Real world example of IIF function

In this example, we will enter marks in IIF condition and will get different grades as per the marks.

DECLARE @Marks int = 95;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
A Grade

GO
DECLARE @Marks int = 80;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
GO
--Result
B Grade

DECLARE @Marks int = 70;
SELECT IIF ( @Marks> 90, 'A Grade', IIF(@Marks>=80 And @Marks<=90,'B Grade','C Grade')) AS Result;
--Result
C Grade

Reference : MSDN

Read Full Post »

SQL Server 2012 – New Logical Function – CHOOSE

Posted in Functions, SQL SERVER, tagged , on August 5, 2012| 1 Comment »

SQL Server 2012 introduced new logical function namely “Choose”. Lets discuss the syntax and purpose of this function in detail.

Syntax:

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



Purpose:


The purpose of this logical function is to return the value from the list as per the index (1 based index). For example, we have four quarters (QUARTER-1 on 1st, QUARTER-2 on 2nd , QUARTER-3 on 3rd and QUARTER-4 on 4th index respectively) and would like to pick the values (Quarters) as per the index.


Note : If choose function will not find any value in a given index, it will return NULL value.


Examples:


Select CHOOSE(0,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL

Select CHOOSE(1,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-1

Select CHOOSE(2,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-2

Select CHOOSE(3,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-3

Select CHOOSE(4,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--QUARTER-4

Select CHOOSE(5,'QUARTER-1','QUARTER-2','QUARTER-3','QUARTER-4') AS [ANNUAL QUARTER]
--OUTPUT
--NULL





					
Read Full Post »