Feeds:
Posts
Comments

Posts Tagged ‘IIF’

SQL SERVER – String comparison as a Boolean in Select statement

Posted in Solutions, SQL SERVER, tagged , , , , on July 24, 2013| Leave a Comment »

Most of the times, we usually maintain the status column of any table in varchar data type. But sometimes due to the requirements, we need to convert this Varchar data type column to Boolean data type to further use it for stats or other purposes as well. But how ?
Before proceeding with the solution, let me first create a sample to demonstrate the solution.

USE tempdb
GO
CREATE TABLE tbl_sample
(
[ID] int,
[Name] varchar(50),
[Status] varchar(50)
)
GO
INSERT INTO tbl_sample VALUES (1,'SQL SERVER 2000','Inactive')
INSERT INTO tbl_sample VALUES (2,'SQL SERVER 2005','Active')
INSERT INTO tbl_sample VALUES (3,'SQL SERVER 2008','Active')
INSERT INTO tbl_sample VALUES (4,'SQL SERVER 2012','Active')
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

stringcomparision

Given below are the solutions.

Solution 1:
In this solution, we will use CASE statement to convert varchar status to boolean.

--This solution is compatibile with SQL Server 2005 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,(CASE WHEN [STATUS]='Active' THEN 1 ELSE 0 END) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.2

Solution 2:
In this solution, we will use IIF statement to convert varchar status to Boolean.

--This solution is compatibile with SQL Server 2012 and above.
SELECT
[ID]
,[NAME]
,[STATUS]
,IIF([STATUS]='Active', 1,0) [Boolean Status]
FROM tbl_sample
GO
--OUTPUT

stringcomparision1.3

Read Full Post »

SQL SERVER 2012 – Detecting Leap Year in T-SQL using SQL SERVER 2012 Functions

Posted in Solutions, SQL SERVER, tagged , , , , , , on January 23, 2013| Leave a Comment »

I was working on statistical reports and I had to display whether the given year was leap year or not in a field. So, I developed few ways using SQL SERVER 2012 functions to detect whether leap year or not.

Given below are the multiple ways to detect leap year:

Method 1 :
In method 1, I made a date (2012-02-28) using DATEFROMPARTS function then used EOMONTH function to find the last date (2012-02-29) of February. Then to find the last day (29), used DAY function and after that used IIF to check whether it is 29 or not, to detect if its a leap year or not.

DECLARE @Year INT =2012
SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 2 :
In method 2, I simply made 2 dates using DATEFROMPARTS function. First is the 1st date of the year and 2nd date is the last date of the year and then used DATEDIFF function to find total no of days in a year and finally used IIF function to check if it is 366 or 365 days. If 366, then leap year and if 365 then it is not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1), DATEFROMPARTS(@year,12,31))+1=366 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 3 :
In method 3, I first made a date(2012-02-28) using DATEFROMPARTS function then added one day to it. After addition I took the day and checked it using IIF function. If it is 29, then leap year, else, it is not.

DECLARE @Year INT =2012
SELECT IIF(DAY(DATEADD(DAY,1,DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Method 4 :
In method 4, I made 2 dates (2012-02-28, 2012-03-01) using DATEFROMPARTS function and then found the date difference using DATEDIFF function, and finally used IIF function to check the difference. If it is 2 days, means the year is leap, else not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,2,28), DATEFROMPARTS(@year,3,01))=2 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Note : Try @Year as 2013 and all the result set will return that 2013 is not a leap year using any above methods.

Let me know if you know a better method to detect leap year.

Read Full Post »