Feeds:
Posts
Comments

Posts Tagged ‘IIF function’

SQL SERVER 2012 – How to check date is End of Month date or not?

Posted in Solutions, SQL SERVER, tagged , , , , , on September 30, 2013| 3 Comments »

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Read Full Post »

SQL SERVER – How to add st, nd, rd & th to dates

Posted in Solutions, SQL SERVER, tagged , , , , , on August 17, 2013| 4 Comments »

Sometimes, date formatting becomes much critical when you know that the format is not supported by the application but to gain the client’s satisfaction, you need to format as per the requirement. I recently came across a date format where I had to add st, nd, rd & th to the dates. Fortunately my client is using SQL Server 2012. So I thought of developing the solution using new functions introduced in SQL Server 2012 to reduce the code and improve the performance.
Let me create a sample to demonstrate the solution.

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample table
CREATE TABLE [dbo].[tbl_sample](
[ID] [int] NULL,
[Date] [date] NULL
)
GO
--Insert records in the table
INSERT INTO dbo.[tbl_sample]
SELECT 1 AS [ID] ,N'2013-08-01' AS [Date] UNION ALL
SELECT 2 AS [ID] ,N'2013-08-02' AS [Date] UNION ALL
SELECT 3 AS [ID] ,N'2013-08-03' AS [Date] UNION ALL
SELECT 4 AS [ID] ,N'2013-08-04' AS [Date] UNION ALL
SELECT 5 AS [ID] ,N'2013-08-05' AS [Date] UNION ALL
SELECT 6 AS [ID] ,N'2013-08-06' AS [Date] UNION ALL
SELECT 7 AS [ID] ,N'2013-08-07' AS [Date] UNION ALL
SELECT 8 AS [ID] ,N'2013-08-08' AS [Date] UNION ALL
SELECT 9 AS [ID] ,N'2013-08-09' AS [Date] UNION ALL
SELECT 10 AS [ID] ,N'2013-08-10' AS [Date] UNION ALL
SELECT 11 AS [ID] ,N'2013-08-11' AS [Date] UNION ALL
SELECT 12 AS [ID] ,N'2013-08-12' AS [Date] UNION ALL
SELECT 13 AS [ID] ,N'2013-08-13' AS [Date] UNION ALL
SELECT 14 AS [ID] ,N'2013-08-14' AS [Date] UNION ALL
SELECT 15 AS [ID] ,N'2013-08-15' AS [Date] UNION ALL
SELECT 16 AS [ID] ,N'2013-08-16' AS [Date] UNION ALL
SELECT 17 AS [ID] ,N'2013-08-17' AS [Date] UNION ALL
SELECT 18 AS [ID] ,N'2013-08-18' AS [Date] UNION ALL
SELECT 19 AS [ID] ,N'2013-08-19' AS [Date] UNION ALL
SELECT 20 AS [ID] ,N'2013-08-20' AS [Date] UNION ALL
SELECT 21 AS [ID] ,N'2013-08-21' AS [Date] UNION ALL
SELECT 22 AS [ID] ,N'2013-08-22' AS [Date] UNION ALL
SELECT 23 AS [ID] ,N'2013-08-23' AS [Date] UNION ALL
SELECT 24 AS [ID] ,N'2013-08-24' AS [Date] UNION ALL
SELECT 25 AS [ID] ,N'2013-08-25' AS [Date] UNION ALL
SELECT 26 AS [ID] ,N'2013-08-26' AS [Date] UNION ALL
SELECT 27 AS [ID] ,N'2013-08-27' AS [Date] UNION ALL
SELECT 28 AS [ID] ,N'2013-08-28' AS [Date] UNION ALL
SELECT 29 AS [ID] ,N'2013-08-29' AS [Date] UNION ALL
SELECT 30 AS [ID] ,N'2013-08-30' AS [Date] UNION ALL
SELECT 31 AS [ID] ,N'2013-08-31' AS [Date]

Solution :
In this solution, I used IIF & Format function introduced in SQL Server 2012.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample
--OUTPUT

date format to st_rd_th.1.1

Let me know if you come across such issues and the proposed solution.

Read Full Post »

SQL Server – Multiple ways to achieve Dynamically Order By Clause

Posted in Solutions, SQL SERVER, tagged , , , , , , , , , , on May 18, 2013| 1 Comment »

‘Dynamically order by’ clause of any query result set is an old topic and has been discussed on multiple forums. Mostly the solution is being achieved using CASE statement. But what encourages me to write this article is that we can achieve the same with shorter code using  IIF & CHOOSE logical functions shipped with SQL Server 2012.

Method 1 :

In this method we will use the traditional approach and dynamically order by the query result set using CASE statement. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CASE
WHEN @ORDERBY=1 THEN FirstName
WHEN @ORDERBY=2 THEN LastName
WHEN @ORDERBY=3 THEN JobTitle
END
GO
--OUTPUT

dynamicorderby1.1

Method 2 :
In this method we will use a NEW approach and dynamically order by the query result set using IIF logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
IIF(@ORDERBY=1,FirstName,IIF(@ORDERBY=2,LastName,JobTitle))
--OUTPUT

dynamicorderby1.1

Method 3 :
In this method we will use yet another NEW approach and dynamically order by the query result set using CHOOSE logical function shipped in SQL Server 2012. Given below is the script.

USE AdventureWorks2012
GO
DECLARE @ORDERBY INT
SET @ORDERBY=3

SELECT
BusinessEntityID
, FirstName
, LastName
, JobTitle
FROM HumanResources.vEmployee
ORDER BY
CHOOSE(@ORDERBY,FirstName,LastName,JobTitle)
GO
--OUTPUT

dynamicorderby1.1

Conclusion :
In all of the above methods we used different methods, functions, approaches but the output will remain same and also it will not impact performance. It just reduces the line of code.

Read Full Post »

SQL SERVER – How To Convert From Boolean(bit) to String

Posted in Solutions, SQL SERVER, tagged , , , , , , on May 14, 2013| Leave a Comment »

Bit (Boolean) data type keeps result in 1 or 0 form in the SQL Server database. But most of the time we need to display 0 as FALSE and 1 as TRUE in front end applications. Given below is the shortest possible solution using IIF function in SQL Server 2012.

You can also convert it using CASE statement but the syntax will be longer.

Given below are the two methods you can use to convert Boolean(bit) data type into string.

Method 1:
In this method, we will use IIF function to convert boolean(bit) to string. IIF function is a new logical function shipped with SQL Server 2012.

--This script is compatible with SQL Server 2012 and above.
USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [Name]
, [ActiveFlag]
, IIF([ActiveFlag]=1,'TRUE','FALSE') AS [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.1

Method 2:
In this method, we will use CASE statement to convert boolean(bit) to string.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
[BusinessEntityID]
, [Name]
, [ActiveFlag]
, CASE WHEN [ActiveFlag]=1 THEN 'TRUE'
WHEN [ActiveFlag]=0 THEN 'FALSE'
END AS  [ActiveFlag As String]
FROM [Purchasing].[Vendor]
GO
--OUTPUT

boolean_to_string1.2

Conclusion :
The only difference between the above two methods is the Syntax. Apart from this, each and every activity, including the performance, will be the same. As per MSDN, IIF is a shorthand form for writing a CASE expression.

Read Full Post »